MCP70-461対策(No.4)
【前提条件】
Microsoft SQL Server 2012 (SP3-CU7) (KB3205051) - 11.0.6579.0 (X64)
1.テストテーブルを作成します。
create table [REF70-461.30392-SalesPerson] ( SalesPersonId int NOT NULL PRIMARY KEY, Name varchar(20) NOT NULL, City varchar(10) NOT NULL, Country varchar(10) NOT NULL ) go create table [REF70-461.30392-Sale] ( SaleId int NOT NULL PRIMARY KEY, SalesPersonId int NOT NULL, Amount money NOT NULL, SaleDate datetime NOT NULL ) go
2.テーブルにデータを入れます。
insert into [REF70-461.30392-SalesPerson] values(1,'APerson','Tokyo','Japan') go insert into [REF70-461.30392-SalesPerson] values(2,'BPerson','Tokyo','Japan') go insert into [REF70-461.30392-SalesPerson] values(3,'CPerson','Osaka','Japan') go insert into [REF70-461.30392-SalesPerson] values(4,'DPerson','New York','US') go insert into [REF70-461.30392-SalesPerson] values(5,'EPerson','New York','US') go insert into [REF70-461.30392-Sale] values(1,1,1000000,'2017-03-05 10:00:00') go insert into [REF70-461.30392-Sale] values(2,2,3000000,'2017-03-02 11:00:00') go insert into [REF70-461.30392-Sale] values(3,1,1000000,'2017-03-02 12:00:00') go insert into [REF70-461.30392-Sale] values(4,3,2000000,'2017-03-05 9:00:00') go insert into [REF70-461.30392-Sale] values(5,4,1000000,'2017-03-04 8:00:00') go insert into [REF70-461.30392-Sale] values(6,2,5000000,'2017-03-10 8:00:00') go insert into [REF70-461.30392-Sale] values(7,5,3000000,'2017-03-08 11:00:00') go insert into [REF70-461.30392-Sale] values(8,5,1000000,'2017-03-07 10:00:00') go insert into [REF70-461.30392-Sale] values(9,5,2000000,'2017-03-06 9:00:00') go insert into [REF70-461.30392-Sale] values(10,5,1000000,'2017-03-05 11:00:00') go insert into [REF70-461.30392-Sale] values(11,1,1000000,'2017-03-05 10:00:00') go insert into [REF70-461.30392-Sale] values(12,2,3000000,'2016-03-02 11:00:00') go insert into [REF70-461.30392-Sale] values(13,1,1000000,'2013-03-02 12:00:00') go insert into [REF70-461.30392-Sale] values(14,3,2000000,'2012-03-05 9:00:00') go insert into [REF70-461.30392-Sale] values(15,4,1000000,'2012-03-04 8:00:00') go insert into [REF70-461.30392-Sale] values(16,2,5000000,'2013-03-10 8:00:00') go insert into [REF70-461.30392-Sale] values(17,5,3000000,'2014-03-08 11:00:00') go insert into [REF70-461.30392-Sale] values(18,5,1000000,'2015-03-07 10:00:00') go insert into [REF70-461.30392-Sale] values(19,5,2000000,'2016-03-06 9:00:00') go insert into [REF70-461.30392-Sale] values(20,5,1000000,'2017-03-05 11:00:00') go
3.(営業担当者、国、都市、年別の売上合計)、(都市、国別の売上合計)、(売上合計)返すselect文をgrouping setsを使用して実行します。
SELECT [REF70-461.30392-SalesPerson].Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM [REF70-461.30392-Sale] INNER JOIN [REF70-461.30392-SalesPerson] ON [REF70-461.30392-Sale].SalesPersonID = [REF70-461.30392-SalesPerson].SalesPersonID GROUP BY GROUPING SETS(([REF70-461.30392-SalesPerson].Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ()) go
結果
Name Country City Year Total -------------------- ---------- ---------- ----------- --------------------- CPerson Japan Osaka 2012 2000000.00 CPerson Japan Osaka 2017 2000000.00 NULL Japan Osaka NULL 4000000.00 APerson Japan Tokyo 2013 1000000.00 APerson Japan Tokyo 2017 3000000.00 BPerson Japan Tokyo 2013 5000000.00 BPerson Japan Tokyo 2016 3000000.00 BPerson Japan Tokyo 2017 8000000.00 NULL Japan Tokyo NULL 20000000.00 NULL Japan NULL NULL 24000000.00 DPerson US New York 2012 1000000.00 DPerson US New York 2017 1000000.00 EPerson US New York 2014 3000000.00 EPerson US New York 2015 1000000.00 EPerson US New York 2016 2000000.00 EPerson US New York 2017 8000000.00 NULL US New York NULL 16000000.00 NULL US NULL NULL 16000000.00 NULL NULL NULL NULL 40000000.00 (19 行処理されました)
- 作者: 朝井淳
- 出版社/メーカー: 技術評論社
- 発売日: 2017/02/18
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
おまけ
GROUP BY GROUPING SETS(())とgroup by rollup(xxx)は似たような使い方ができます。
group by cubeはすべての組み合わせを集計してくれます。
例:
a = 1
b=1, 2
group by cube (a, b)
a b
1 1
1 2
1 N/A
N/A N/A
参考: