博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
[MSSQL]NTILE另类分页有么有?!
阅读量:6328 次
发布时间:2019-06-22

本文共 6931 字,大约阅读时间需要 23 分钟。

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片

 

见笑了

 

 

猜测您可能对下边的文章感兴趣

如果您喜欢该博客请点击右下角推荐按钮,您的推荐是作者创作的动力!

转载地址:http://geyoa.baihongyu.com/

你可能感兴趣的文章
Hive学习之路 (七)Hive的DDL操作
查看>>
[转]mysql使用关键字作为列名的处理方式
查看>>
awesome go library 库,推荐使用的golang库
查看>>
树形展示形式的论坛
查看>>
jdbcTemplate 调用存储过程。 入参 array 返回 cursor
查看>>
C++中的stack类、QT中的QStack类
查看>>
Linux常用基本命令[cp]
查看>>
CSS 相对|绝对(relative/absolute)定位系列(一)
查看>>
关于 Nginx 配置 WebSocket 400 问题
查看>>
Glide和Govendor安装和使用
查看>>
Java全角、半角字符的关系以及转换
查看>>
Dubbo和Zookeeper
查看>>
前端项目课程3 jquery1.8.3到1.11.1有了哪些新改变
查看>>
UOJ#179. 线性规划(线性规划)
查看>>
整合spring cloud云架构 - SSO单点登录之OAuth2.0登录认证(1)
查看>>
Isolation Forest原理总结
查看>>
windows的服务中的登录身份本地系统账户、本地服务账户和网络服务账户修改
查看>>
JAVA中循环删除list中元素的方法总结
查看>>
redis 安装
查看>>
SQL some any all
查看>>