In my project, all SQL must be performed in JPA’s QueryDsl format for database operations. There is a slightly more complicated one. There is relatively little information on the Internet about nested subqueries, and the subqueries are also very simple.
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 ListtopBind() { 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");
There should be no need to explain this. QueryDsl will automatically generate an entity class with a capital Q. tm_match_report_user is Table Name.
If you need children's shoes, you can refer to them. They are basically the same.
NumberTemplatetemplate = Expressions .numberTemplate(Integer.class, "rank() over(order by nvl(dd.anncount, 0) desc)");
NumberTemplatetemplate = Expressions .numberTemplate(Integer.class, "rank() over(order by nvl(sum({0}), 0) desc)",quser.opening_Count);
The above is the detailed content of How to solve the problem of nested subquery of QueryDsl in SpringBoot. For more information, please follow other related articles on the PHP Chinese website!