综合练习： PIVOT、UNPIVOT、GROUPING SETS、GROUPING_ID_1

综合练习： PIVOT、UNPIVOT、GROUPING SETS、GROUPING_ID

 

 

 问题1：
Desired output:

 

empid       cnt2007     cnt2008     cnt2009
----------- ----------- ----------- -----------
1           1           1           1
2           1           2           1
3           2           0           2

问题2：

Desired output:

empid       orderyear   numorders
----------- ----------- -----------
1           2007        1
1           2008        1
1           2009        1
2           2007        1
2           2008        2
2           2009        1
3           2007        2
3           2009        2

问题3：Write a query against the Orders table that returns the total quantities for each:
　　　　(employee, customer, and order year),
　　　　(employee and order year),
　　　　(customer and order year).
Include a result column in the output that uniquely identifies the grouping set with which the current row is associated

Desired output:

 

groupingset    empid       custid    orderyear   sumqty
-------------- ----------- --------- ----------- -----------
0              2           A         2007        12
0              3           A         2007        10
4              NULL        A         2007        22
0              2           A         2008        40
4              NULL        A         2008        40
0              3           A         2009        10
4              NULL        A         2009        10
0              1           B         2007        20
4              NULL        B         2007        20
0              2           B         2008        12
4              NULL        B         2008        12
0              2           B         2009        15
4              NULL        B         2009        15
0              3           C         2007        22
4              NULL        C         2007        22
0              1           C         2008        14
4              NULL        C         2008        14
0              1           C         2009        20
4              NULL        C         2009        20
0              3           D         2009        30
4              NULL        D         2009        30
2              1           NULL      2007        20
2              2           NULL      2007        12
2              3           NULL      2007        32
2              1           NULL      2008        14
2              2           NULL      2008        52
2              1           NULL      2009        20
2              2           NULL      2009        15
2              3           NULL      2009        40

f object_id('dbo.orders','U') is not null drop table dbo.orders;
GO
CREATE TABLE dbo.Orders
(
  orderid   INT         NOT NULL,
  orderdate DATE        NOT NULL,
  empid     INT         NOT NULL,
  custid    VARCHAR(5)  NOT NULL,
  qty       INT         NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
GO
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),

/*
    《Microsoft SQL Server 2008 T-SQL Fundamentals》
*/
------------------------------------------------------------------
select *
from Orders
------------------------------------------------------------------
--按照empid分组
select empid
from Orders
group by empid
------------------------------------------------------------------
--按YY分组
select datepart(yy,orderdate)
from Orders
group by datepart(yy,orderdate)
------------------------------------------------------------------

------------------------------------------------------------------
--按empid, orderid,YY分组
select empid, orderid, datepart(yy,orderdate),
           count(*)
from Orders
group by empid, orderid, datepart(yy,orderdate)
------------------------------------------------------------------
----按empid, YY分组
select empid,  datepart(yy,orderdate) as YY,
           count(*) as cnt
from Orders
group by empid,  datepart(yy,orderdate)
------------------------------------------------------------------
----按empid, YY分组
select empid,  YEAR(orderdate) as YY    --, count(*) as cnt
from Orders
------------------------------------------------------------------
        --从上面这个步骤，直接得出最后的结果，交叉时之前的子查询不要分组，统计，   还是多熟悉一下pivot，unpivot的语法
        select empid, [2007], [2008], [2009]
        from (                          
                    select empid,  datepart(yy,orderdate) as YY
                    from Orders
                ) as d
            pivot(count(YY) for YY in([2007], [2008], [2009])) as p    --对于交叉时，一直不想在这里用列举的方法，假使这里的值很多时？
------------------------------------------------------------------
            ----按empid, YY分组，不需要的思考过程，就向后缩进
            --select empid, -- datepart(yy,orderdate),
            --           (case when datepart(yy,orderdate)='2007' then count(*) end) AS cnt2007,
            --           (case when datepart(yy,orderdate)='2008' then count(*) end) AS cnt2008,
            --           (case when datepart(yy,orderdate)='2009' then count(*) end) AS cnt2009
            --from Orders
            --group by empid,  datepart(yy,orderdate)
------------------------------------------------------------------
----按empid，这就是最后的结果，虽然是最后的结果，是以订单数量的次数在相加
select empid, -- datepart(yy,orderdate),
          count(case when datepart(yy,orderdate)='2007' then qty end) AS cnt2007,
          count(case when datepart(yy,orderdate)='2008' then qty end) AS cnt2008,
          count(case when datepart(yy,orderdate)='2009' then qty end) AS cnt2009
from Orders
group by empid
------------------------------------------------------------------
----按empid，这就是最后的结果，以出现的 “年” 相同的次数在相加
select empid, -- datepart(yy,orderdate),
          count(case when year(orderdate)='2007' then year(orderdate) end) AS cnt2007,
          count(case when year(orderdate)='2008' then year(orderdate) end) AS cnt2008,
          count(case when year(orderdate)='2009' then year(orderdate) end) AS cnt2009
from Orders
group by empid
------------------------------------------------------------------
--将查询的结果集，插入到另外一个表中去，目的是什么？ ---看这么查询得出这么整齐的结果，当然是想训练UNPIVOT
IF OBJECT_ID('dbo.EmpYearOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpYearOrders;

SELECT empid, [2007] AS cnt2007, [2008] AS cnt2008, [2009] AS cnt2009
INTO dbo.EmpYearOrders
FROM (SELECT empid, YEAR(orderdate) AS orderyear
      FROM dbo.Orders) AS D
  PIVOT(COUNT(orderyear)
        FOR orderyear IN([2007], [2008], [2009])) AS P;

SELECT * FROM dbo.EmpYearOrders;
    empid       cnt2007     cnt2008     cnt2009
    ----------- ----------- ----------- -----------
     1           1           1          1
     2           1           2          1
     3           2           0          2
------------------------------------------------------------------
--将上面这个结果，转化为下面的，打散开
empid       orderyear   numorders
----------- ----------- -----------
1           2007        1
1           2008        1
1           2009        1
2           2007        1
2           2008        2
2           2009        1
3           2007        2
3           2009        2

SELECT empid, orderyear, numorders
FROM dbo.EmpYearOrders
  UNPIVOT(numorders 
                   for orderyear in(cnt2007, cnt2008,cnt2009)) AS U
where numorders <> 0
------------------------------------------------------------------

------------------------------------------------------------------
--
--select * from Orders
select grouping(empid) as groupingset, empid, custid, year(orderdate) as orderyear, sum(qty) as sumqty
from Orders
group by
    grouping sets(
                            (empid, custid, year(orderdate)),
                            (empid,  year(orderdate)),
                            (custid, year(orderdate))      --如果分组包含()，则结果集中将会计算总的数量（sumqty = 205)
                         )
------------------------------------------------------------------
-- Write a query against the Orders table that returns the total quantities for each: 
-- (employee, customer, and order year), (employee and order year), (customer and order year). 
-- Include a result column in the output that uniquely identifies the grouping set with which the current row is associated. 
-- 用GROUPING_ID函数为与每一行相关联的分组集生成唯一的标识符
select grouping_id(empid,custid,year(orderdate)) as groupingset, empid, custid, year(orderdate) as orderyear, sum(qty) as sumqty
from Orders
group by
    grouping sets(
                   (empid, custid, year(orderdate)),
                   (empid,  year(orderdate)),
                   (custid, year(orderdate))                        
                 )

 感悟：也许国内出书都是以结果为导向，或者为升职、或者为名，反正出的书、或者翻译的书籍，即使自己懂、理解、或没完全理解透彻，翻译出来的书籍都不是那么理想的，
            并不是在否定他们的劳动成果，你出书，面向哪个级别的书籍，就应该针对哪个级别要进行理解性的讲解。

--T-SQL技术内幕可参考：中文版、可结合英文版chm格式看