我正在使用，我想進行子查詢。這是普通查詢QueryDsl SQLleft joinSQLSELECT usr.memberId, payoutsBbf.totalPytsFROM 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我如何處理零件LEFT JOIN x ON如何處理部件中的子查詢別名SELECT payoutBbf.totalPyts
2 回答
茅侃侃
TA貢獻1842條經驗 獲得超21個贊
這就是我會怎么做的：
final StringPath payoutsBbf = stringPath("payoutsBbf");
final String mmbId = "mmb_id";
final String totalPyts = "totalPyts";
sqlQueryFactory.select(users.memberId, stringPath(payoutsBbf, totalPyts))
.from(users).leftJoin(
sqlQueryFactory.select(payout.member_id.as(mmbId), member_id.amount.sum().as(totalPyts))
.from(payout).where(payout.payoutPeriod.lt("2018-01-01")).groupBy(payout.member_id),
payoutsBbf
).on(users.id.eq(stringPath(payoutsBbf, mmbId))).fetch();
富國滬深
TA貢獻1790條經驗 獲得超9個贊
我認為這對你有用。雖然這有點笨拙：
SQLQueryFactory sqlqf; // Should be @Autowired
QUsers 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)
.from(qpayouts)
.where(qpayouts.payoutPeriod.lt("2018-01-01")) // Use date object
.groupBy(qpayouts.memberId);
final SimplePath<? extends SQLQuery> payoutsBBfPath = Expressions.path(payoutsBBfPath.getClass(), "payoutsBbf");
List<Tuple> fetch = sqlqf.select(
qusers.memberId,
Expressions.path(payoutsBbf.getClass(), new PathMetadata(payoutsBBfPath, "totalPayouts", PathType.PROPERTY))
)
.from(qusers)
.leftJoin(payoutsBbf, payoutsBBfPath)
.addJoinFlag(" on payoutsBbf.mmb_id = users.id", JoinFlag.Position.BEFORE_CONDITION)
.fetch();
請注意使用定義為 的別名來指定連接列。另請注意使用 在部分中指定子列payoutsBbfExpressions.path()select()
添加回答
舉報
0/150
提交
取消