楼主项目中要求所有的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