SQL统计——按照各种维度

r囧r小猫 2021-06-26 16:06 442阅读 0赞

在SQLserver中可以按照各种维度进行统计，实现与EXCLE一样强大的功能。

--==========================
    --Blog:<奔跑的金鱼>
    --Desc:<SQL统计>
    --Date:<2015-01-07>
    --==========================
    /*描述：不同员工在不同时间参加不同项目组中，获得的收入*/
    IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID(N'tb_Income') and XTYPE='U')
    DROP TABLE tb_Income
     
    CREATE TABLE [tb_Income]
    (
      [TeamID] int not null,
      [PName]  Nvarchar(20) NOT NULL,
      [CYear]  Smallint NOT NULL,
      [CMonth] TinyInt NOT NULL,
      [CMoney] Decimal (10,2) Not Null
    )
     
    ---------------------插入测试数据
    INSERT [dbo].[tb_Income]
    SELECT 1,'荆天明',2011,2,5600
    union ALL SELECT 1,'荆天明',2011,1,5678
    union ALL SELECT 1,'荆天明',2011,3,6798
    union ALL SELECT 2,'荆天明',2011,4,7800
    union ALL SELECT 2,'荆天明',2011,5,8899
    union ALL SELECT 3,'荆天明',2012,8,8877  
     
    union ALL SELECT 1,'高月',2011,1,3455
    union ALL SELECT 1,'高月',2011,2,4567
    union ALL SELECT 2,'高月',2011,3,5676
    union ALL SELECT 3,'高月',2011,4,5600
    union ALL SELECT 2,'高月',2011,5,6788
    union ALL SELECT 2,'高月',2012,6,5679
    union ALL SELECT 2,'高月',2012,7,6785  
     
    union ALL SELECT 2,'端木蓉',2011,2,5600
    union ALL SELECT 2,'端木蓉',2011,3,2345
    union ALL SELECT 2,'端木蓉',2011,5,12000
    union ALL SELECT 3,'端木蓉',2011,4,23456
    union ALL SELECT 3,'端木蓉',2011,6,4567
    union ALL SELECT 1,'端木蓉',2012,7,6789
    union ALL SELECT 1,'端木蓉',2012,8,9998  
     
    union ALL SELECT 3,'高渐离',2011,7,6798
    union ALL SELECT 3,'高渐离',2011,10,10000
    union ALL SELECT 3,'高渐离',2011,9,12021
    union ALL SELECT 2,'高渐离',2012,11,8799
    union ALL SELECT 1,'高渐离',2012,12,10002  
     
    union ALL SELECT 3,'少司命',2011,8,7896
    union ALL SELECT 3,'少司命',2011,9,7890
    union ALL SELECT 2,'少司命',2011,10,7799
    union ALL SELECT 2,'少司命',2011,11,9988
    union ALL SELECT 2,'少司命',2012,9,34567
    union ALL SELECT 3,'少司命',2012,12,5609 
     
    --------------------------------查询表中数据
    select * from tb_Income

![在这里插入图片描述][20210330170537673.png]

/*CUBE运算符生成的结果集是多维数据集,多维数据集是事实数据的扩展,事实数据即记录个别时间的数据,扩展建立在用户准备分析的列上,这些列被称为维,多维数据集是一个结果集,其中包含各纬度所有可能的交叉表格*/
     
    ------------------------------使用单个维度进行统计
    SELECT PName as 员工姓名,
    SUM(CMoney) 总收入
    FROM tb_Income
    GROUP BY CUBE (PName)

![在这里插入图片描述][20210330170730920.png]

------------------------------使用多个维度进行统计
    SELECT TeamID As 项目组编号,PName as 员工姓名,
    SUM(CMoney) 总收入
    FROM tb_Income
    GROUP BY CUBE (PName,TeamID)

![在这里插入图片描述][20210330171242980.png]

上面是按照`CUBE`进行统计的，接下来是使用`ROLLUP`进行统计

-------------------------------使用ROLLUP进行统计
    SELECT TeamID As 项目组编号,PName as 员工姓名,
    SUM(CMoney) 总收入
    FROM tb_Income
    GROUP BY ROLLUP (PName,TeamID)

![在这里插入图片描述][20210330170908593.png]

*  `CUBE` 生成的结果集显示了所选列中值的所有组合的聚合。
 *  `ROLLUP` 生成的结果集显示了所选列中值的某一层次结构的聚合。

[20210330170537673.png]: /images/20210626/1441f66da6614e9e9fb33904bfb4de23.png
[20210330170730920.png]: /images/20210626/f521a062289740d882e529c418b8f21d.png
[20210330171242980.png]: /images/20210626/136c5f9c32eb479ebddde9b9aed6a84b.png
[20210330170908593.png]: /images/20210626/9f87f889cb5b4a80b952692de75ba6d4.png