[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片

 

见笑了

 

 

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

SQL SERVER 2008 函数大全 - 字符串函数

SQL2008系统统计函数

[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验

[MSSQL]ROW_NUMBER函数

[MSQL]RANK函数

[MSSQL]NTILE另类分页有么有?!

[MSQL]也说SQL中显示星期几函数

[MSSQL]COALESCE与ISNULL函数

[MSSQL]PIVOT函数

[MSSQL]FOR XML AUTO I

[MSSQL]FOR XML AUTO II

[MSSQL]TRY…CATCH…通用格式

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