SpringBoot之QueryDsl嵌套子查询问题怎么解决

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 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 crm_Province = Expressions.template(String.class, "dd.crm_Province");
 
    SimpleTemplate sscrm_Province = Expressions.template(String.class, "ss.crm_Province");
    SimpleTemplate province_Name = Expressions.template(String.class, "dd.province_Name");
    NumberTemplate anncount = Expressions.numberTemplate(Long.class, "nvl(dd.anncount, 0)");
    NumberTemplate yicansai = Expressions.numberTemplate(Long.class, "nvl(dd.yicansai, 0)");
    NumberTemplate weicansai = Expressions.numberTemplate(Long.class, "nvl(ss.weicansai, 0)");
    NumberTemplate 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 template = Expressions
                   .numberTemplate(Integer.class,
                   "rank() over(order by nvl(dd.anncount, 0) desc)");

还可以通过占位符的样式来写

NumberTemplate template = Expressions
                    .numberTemplate(Integer.class, 
                    "rank() over(order by nvl(sum({0}), 0) desc)",quser.opening_Count);