SpringBoot之QueryDsl巢狀子查詢問題怎麼解決

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

/** * 复杂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");

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);