SQL SEVER (ROLLUP与CUBE，ROW_NUMBER())使用方法

 

1.建立测试专用数据；

if object_id('TESTDB') is not null drop table TESTDB

create table TESTDB(A varchar(8), B INT)

insert into TESTDB

select 'A1', 6 union all

select 'A1', 8 union all

select 'A1', 67 union all

select 'A2', 67 union all

select 'A2', 4 union all

select 'A2', 112 union all

select 'A3', 90 union all

 

此处：可以用SELECT 与 UNION联合的插入方法，方便快捷，前提是插入数据的列与表的列相同；

2.ROW_NUMBER() 使用

   ROW_NUMBER()的使用，主要是对数据进行分组时，对组内的数据进行排序，对临时表的内容进行测试，语句如下：

   

SELECT * ,ROW_NUMBER() OVER( PARTITION BY A ORDER BY B DESC) NUM FROM TESTDB GROUP BY A,B 

 

 

 

执行结果如下：

分析：语句根据A、B进行分组，分组后内部按照A进行分区,B列进行排序，增添NUM计数信息；

3.ROLLUP 与 CUBE 的对比

首先：对于进行ROULLUP排序；

SELECT * ,ROW_NUMBER() OVER( PARTITION BY A ORDER BY B DESC) NUM FROM TESTDB GROUP BY A,B WITH ROLLUP

 

分析：与无ROLLUP对比，多出来了四行数据，四行数据分别为：

1).单独对A进行分组，忽略B对其用NULL代替；

2）忽略A、B进行分组，即多出一行空值；

以此类推的话：如果是三列A、B、C进行ROLLUP的话，应该是先对A进行分组，再对A、B分组，最后是A、B、C均不考虑就是空值一行，随后测试猜想；对表进行列的增加；

create table TESTDB(A varchar(8), B INT,C varchar(8))

insert into TESTDB

select 'A1', 6,'A' union all

select 'A1', 8,'B' union all

select 'A1', 67,'C' union all

select 'A2', 67,'A' union all

select 'A2', 4 ,'B' union all

select 'A2', 112, 'C' union all

select 'A3', 90, 'D' union all
select 'A3', 6,'D'
SELECT * FROM TESTDB GROUP BY A,B,C WITH ROLLUP

 

      猜想正确，总结出规律为：分别对group分组的列按照顺序，依次组合进行分组，A、B、C则为三个均不考虑，只考虑A，考虑A、B，考虑A、B、C排序即为ROLLUP 的内在逻辑。

 4.CUBE的使用

    对两列时进行CUBE排序与ROLLUP进行对比

 

SELECT * ,ROW_NUMBER() OVER( PARTITION BY A ORDER BY B DESC) NUM FROM TESTDB GROUP BY A,B WITH CUBE

 

 

 

 

 

对比得知：CUBE排序方式为

                       GROUP BY   A

                       GROUP BY B

                       GROUP BY A、B

                       NULL行

              ROLLUP为： 

                        NULL行

                        GROUP BY   A

                       GROUP BY A、B

CUBE要比ROLLUP分别排序的全，内部元素均会一一排序；ROLLUP则为顺序排序。