내 프로젝트에서는 데이터베이스 작업을 위해 모든 SQL이 JPA의 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<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의 중첩 하위 쿼리 문제를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!