查询Dsl SQL - 左联接子查询

Question

我正在使用，我想进行子查询。这是普通查询QueryDsl SQLleft joinSQL

SELECT

  usr.memberId,

  payoutsBbf.totalPyts

FROM

  users usr

  LEFT JOIN

    (SELECT

       pyt.member_id   AS mmb_id,

       SUM(pyt.amount) AS totalPyts

  FROM

    payout pyt

  WHERE

    pyt.payoutPeriod < '2018-01-01'

    GROUP BY pyt.member_id) AS payoutsBbf ON usr.id = payoutsBbf.mmb_id

我希望把它写进去，并且几乎重要的是，子查询被留在主表中，因为这只是整个复杂查询的一个片段。QueryDsl SQLusers

1. 我如何处理零件LEFT JOIN x ON

2. 如何处理部件中的子查询别名SELECT payoutBbf.totalPyts




Answer 1

这就是我会怎么做的：final StringPath payoutsBbf = stringPath("payoutsBbf");final String mmbId = "mmb_id";final String totalPyts = "totalPyts";sqlQueryFactory.select(users.memberId, stringPath(payoutsBbf, totalPyts))&nbsp; &nbsp; .from(users).leftJoin(&nbsp; &nbsp; &nbsp; &nbsp; sqlQueryFactory.select(payout.member_id.as(mmbId), member_id.amount.sum().as(totalPyts))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .from(payout).where(payout.payoutPeriod.lt("2018-01-01")).groupBy(payout.member_id),&nbsp; &nbsp; &nbsp; &nbsp; payoutsBbf&nbsp; &nbsp; ).on(users.id.eq(stringPath(payoutsBbf, mmbId))).fetch();

Answer 2

我认为这对你有用。虽然这有点笨拙：SQLQueryFactory sqlqf; // Should be @AutowiredQUsers qusers = new QUsers();QPayouts qpayouts = new QPayouts();Expression<Long> memberId = ExpressionUtils.as(qpayouts.memberId, "mmb_id");Expression<Double> totalPayouts = ExpressionUtils.as(qpayouts.amount.sum(), "totalPayouts");SQLQuery<Tuple> payoutsBbf = SQLExpressions.select(memberId, totalPayouts)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .from(qpayouts)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .where(qpayouts.payoutPeriod.lt("2018-01-01")) // Use date object&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .groupBy(qpayouts.memberId);final SimplePath<? extends SQLQuery> payoutsBBfPath = Expressions.path(payoutsBBfPath.getClass(), "payoutsBbf");List<Tuple> fetch = sqlqf.select(&nbsp; &nbsp; &nbsp; &nbsp; qusers.memberId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; Expressions.path(payoutsBbf.getClass(), new PathMetadata(payoutsBBfPath, "totalPayouts", PathType.PROPERTY))&nbsp; &nbsp; )&nbsp; &nbsp; .from(qusers)&nbsp; &nbsp; .leftJoin(payoutsBbf, payoutsBBfPath)&nbsp; &nbsp; .addJoinFlag(" on payoutsBbf.mmb_id = users.id", JoinFlag.Position.BEFORE_CONDITION)&nbsp; &nbsp; .fetch();请注意使用定义为 的别名来指定连接列。另请注意使用 在部分中指定子列payoutsBbfExpressions.path()select()