[MSSQL]NTILE另类分页有么有?!
NTILE这个关键字干啥妮?ORACLE管它叫分片,大概意思是把查询出来的结果集分成尽量均等的组(片),有点不直观,来看代码
先看测试用的数据表:
--DROP TABLE T
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL)
SELECT 'a1','b1','c1',10 union all
SELECT 'a1','b1','c2',10 union all
SELECT 'a1','b2','c2',40 union all
SELECT 'a1','b2','c3',40 union all
SELECT 'a1','b2','c3',50 union all
SELECT 'a2','b3','c3',12 union all
SELECT 'a2','b3','c3',22 union all
SELECT 'a2','b3','c3',32
SELECT * FROM T
--------------------------------------------------------------------------
GRP_A GRP_B GRP_C VAL
-------------------- -------------------- -------------------- -----------
a1 b1 c1 10
a1 b1 c2 10
a1 b2 c2 40
a1 b2 c3 40
a1 b2 c3 50
a2 b3 c3 12
a2 b3 c3 22
a2 b3 c3 32
(8 行受影响)
试下这个分片功能,SQL脚本比较简单:
SELECT
*,
NTILE(3)OVER(ORDER BY VAL) AS NUM
FROM T
GRP_A GRP_B GRP_C VAL NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 1
a2 b3 c3 12 1
a2 b3 c3 22 2
a2 b3 c3 32 2
a1 b2 c2 40 2
a1 b2 c3 40 3
a1 b2 c3 50 3
(8 行受影响)
结果集一共八条,分成了111,222,33一共三片,如果是九条刚好分配好,真不巧,八条,最后一片少一个,这没关系啊,
它就这么个功能,灰常简单,再来分个四片看看,分四片完美均分!
SELECT
*,
NTILE(4)OVER(ORDER BY VAL) AS NUM
FROM T
GRP_A GRP_B GRP_C VAL NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 1
a2 b3 c3 12 2
a2 b3 c3 22 2
a2 b3 c3 32 3
a1 b2 c2 40 3
a1 b2 c3 40 4
a1 b2 c3 50 4
(8 行受影响)
如上所示,刚好11,22,33,44分成四片
分页的又一方法对啵?比较不常见哈,因为你不知道总记录有多少
假设我们总想分成100页,不考虑每页多少条,这个场景下真可以用NTILE关键字!传100进去即可!
因为这里一共八条,所以传100进行,一行记录一页都不够,于是产生了ROW_NUMBER的效果,示例
SELECT
*,
NTILE(100)OVER(ORDER BY VAL) AS NUM
FROM T
GRP_A GRP_B GRP_C VAL NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 2
a2 b3 c3 12 3
a2 b3 c3 22 4
a2 b3 c3 32 5
a1 b2 c2 40 6
a1 b2 c3 40 7
a1 b2 c3 50 8
(8 行受影响)
NTILE的另类分页
头脑风暴一下,总记录数可以COUNT查询得知,每页大小已知,是不是可以计算得出多少页来?
DECLARE @TOTAL_COUNT INT,@PAGE_COUNT INT
SET @TOTAL_COUNT = 8
SET @PAGE_COUNT = 3
总页数 = @TOTAL_COUNT/@PAGE_COUNT
是不是这样,总记录数/每页大小=总页数,然后妮?你想干啥?想分页...继续
知道总页数了吧,分片呀,呵呵
SELECT
*,
NTILE(@TOTAL_COUNT/@PAGE_COUNT)OVER(ORDER BY VAL) AS NUM
FROM T
刚试了把,NTILE参数可以是表达式滴,于是全新的分页出来啦~,以上代码执行后结果如下
GRP_A GRP_B GRP_C VAL NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 1
a2 b3 c3 12 1
a2 b3 c3 22 1
a2 b3 c3 32 2
a1 b2 c2 40 2
a1 b2 c3 40 2
a1 b2 c3 50 2
(8 行受影响)
然后对外边直接传第几页,就返回NUM等于几的数据,如NUM=2表示第二片的数据,爽不爽?不用再计算@PageIndex * @PageSize然后BETWEEN算(@PageIndex+1) * @PageSize谁用谁知道..
传统分页代码,也不传统啊,ROW_NUMBER分页脚本:
DECLARE @PageSize INT,@PageIndex INT
SELECT @PageIndex = 0,@PageSize = 2
SELECT * FROM
(
SELECT
*,
ROW_NUMBER()OVER(ORDER BY VAL) AS NUM
FROM T
) AS T
WHERE NUM BETWEEN @PageIndex * @PageSize AND (@PageIndex + 1) * @PageSize
NTILE另类分页:
DECLARE @TOTAL_COUNT INT,@PAGE_SIZE INT
SET @TOTAL_COUNT = 8
SET @PAGE_SIZE = 2
----总页数 = @TOTAL_COUNT/@PAGE_COUNT
SELECT * FROM
(
SELECT
*,
NTILE(@TOTAL_COUNT/@PAGE_SIZE)OVER(ORDER BY VAL) AS NUM
FROM T
)AS T
WHERE NUM = 3
功能是一样滴啊,除了分页还能揍啥?它的功能就是这么个功能,能揍啥,自己发挥吧
NTILE与PARTITION的配合使用:
SELECT
*,
NTILE(4)OVER(PARTITION BY GRP_A ORDER BY VAL ASC) AS NUM
FROM T
GRP_A GRP_B GRP_C VAL NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 1
a1 b2 c2 40 2
a1 b2 c3 40 3
a1 b2 c3 50 4
a2 b3 c3 12 1
a2 b3 c3 22 2
a2 b3 c3 32 3
(8 行受影响)
也没什么特别之处,就是先分组,再排序,最后分片,
这里是先按GRP_A分组,分成了a1,a2两组,在每组内再按VAL排序,最后在每组内再分片,a1组分成了11,2,3,4四片,a2组分成了1,2,3片
见笑了
猜测您可能对下边的文章感兴趣
[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验
如果您喜欢该博客请点击右下角推荐按钮,您的推荐是作者创作的动力!
------------------------------------------
除非特别声明,文章均为原创,版权与博客园共有,转载请保留出处
BUY ME COFFEE
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· 单线程的Redis速度为什么快？
· 展开说说关于C#中ORM框架的用法！
· Pantheons：用 TypeScript 打造主流大模型对话的一站式集成库