Projek saya memerlukan semua SQL mestilah dalam format QueryDsl JPA untuk operasi pangkalan data, dan salah satu daripadanya adalah lebih rumit di sana adalah maklumat yang agak sedikit di Internet tentang subqueries bersarang, dan subqueries juga sangat mudah.
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<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");
Sepatutnya tidak perlu menjelaskan perkara ini secara automatik dengan modal Q. tm_match_report_user ialah Nama jadual.
Jika anda memerlukan kasut kanak-kanak, anda boleh merujuk kepada mereka, ia pada asasnya sama.
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);
Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan masalah subquery bersarang QueryDsl dalam SpringBoot. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!