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格式 /** * 复杂sql查询 -- 双子查询join * @return */ 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);
以上就是springboot之querydsl嵌套子查询问题怎么解决的详细内容。