需求场景:前端要求数据分页并返回没有分页的数据总量。
一般是通过两次查询返回。
解决办法:CET + 开窗实现。
通过count开窗计算出总的数据量,通过row_number开窗对数据进行排序用于分页过滤
with tmp as (sql复杂查询出数据)
select *
from (select *,
row_number() over(order by case when school_id = -1 then 0 else 1 end,school_name) row_num,
#{page_num} page_num,
#{page_size} page_size,
count() over() total_cnt
from tmp
)
where row_num > (#{page_num} - 1) * #{page_size} AND row_num <= #{page_num} * #{page_size}
其中:
order by case when school_id = -1 then 0 else 1 end,school_name
是为了满足:指定数据排列在第一行