QueryDsl嵌套子查询
我项目中要求所有的SQL必须以JPA的QueryDsl格式进行数据库操作，其中有一个稍微复杂点的嵌套子查询，网上资料比较少，而且子查询也特别简单。
费了好大的劲才搞出来，现贴出来供大家参考。
这是MyBatis的查询格式
select dd.crm_province as 省编码,
dd.province_name as 省份,
nvl(dd.anncount, 0) as 年化收入,
nvl(dd.yicansai, 0) as 已参赛人数,
nvl(ss.weicansai, 0) as 未参赛人数,
rank() over(order by nvl(dd.anncount, 0) desc) as 排名
from (select ll.crm_province,
ll.province_name,
nvl(sum(ll.annuali_count), 0) anncount,
count(distinct ll.staff_id) yicansai
from tm_match_report_user ll
where ll.is_close = 1
and ll.role_id = 00
and ll.is_login = 1
group by ll.crm_province, ll.province_name) dd
left join (select ll.crm_province, count(distinct ll.staff_id) weicansai
from tm_match_report_user ll
where ll.is_close = 1
and ll.role_id = 00
and ll.is_login = 0
group by ll.crm_province) ss
on dd.crm_province = ss.crm_province
order by dd.anncount desc这是JPA的QueryDsl格式
public List<StaffRank> topBind() {
QMatchReportUser user = new QMatchReportUser("tm_match_report_user");
QMatchReportUser user1 = new QMatchReportUser("tm_match_report_user");
StringPath dd = Expressions.stringPath("dd");
StringPath ss = Expressions.stringPath("ss");
SimpleTemplate<String> crm_Province = Expressions.template(String.class, "dd.crm_Province");
SimpleTemplate<String> sscrm_Province = Expressions.template(String.class, "ss.crm_Province");
SimpleTemplate<String> province_Name = Expressions.template(String.class, "dd.province_Name");
NumberTemplate<Long> anncount = Expressions.numberTemplate(Long.class, "nvl(dd.anncount, 0)");
NumberTemplate<Long> yicansai = Expressions.numberTemplate(Long.class, "nvl(dd.yicansai, 0)");
NumberTemplate<Long> weicansai = Expressions.numberTemplate(Long.class, "nvl(ss.weicansai, 0)");
NumberTemplate<Integer> template = Expressions
.numberTemplate(Integer.class, "rank() over(order by nvl(dd.anncount, 0) desc)");
OrderSpecifier order = new OrderSpecifier(Order.DESC,
Expressions.template(String.class, "dd.anncount"));
SubQueryExpression query = SQLExpressions
.select(user.crm_Province, user.province_Name,
user.annuali_Count.sum().as("anncount"),
user.staff_Id.countDistinct().as("yicansai"))
.from(user)
.where(user.is_CLOSE.eq(1), user.role_Id.eq("00"), user.is_Login.eq("1"))
.groupBy(user.crm_Province, user.province_Name);
SubQueryExpression query1 = SQLExpressions
.select(user1.crm_Province, user1.staff_Id.countDistinct().as("weicansai"))
.from(user1)
.where(user1.is_CLOSE.eq(1), user1.role_Id.eq("00"), user1.is_Login.eq("0"))
.groupBy(user1.crm_Province);
return factory.select(
Projections.bean(StaffRank.class,
crm_Province.as("crm_Province"),
province_Name.as("province_Name"), anncount.as("anncount"),
yicansai.as("yicansai"), weicansai.as("weicansai"), template.as("rank")))
.from(query, dd).leftJoin(query1, ss).on(crm_Province.eq(sscrm_Province)).orderBy(order)
.fetch();
}QMatchReportUser user = new QMatchReportUser("tm_match_report_user");这个应该不用解释了，QueryDsl会自动生成大写Q的实体类，tm_match_report_user是表名。
有需要的童鞋可以参考下，基本上大同小异。
需要注意的是
NumberTemplate<Integer> template = Expressions
.numberTemplate(Integer.class,
"rank() over(order by nvl(dd.anncount, 0) desc)");还可以通过占位符的样式来写
NumberTemplate<Integer> template = Expressions
.numberTemplate(Integer.class,
"rank() over(order by nvl(sum({0}), 0) desc)",quser.opening_Count);总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
