Country VARCHAR ( 100 ),
[ State ] VARCHAR ( 100 ),
City VARCHAR ( 100 ),
[ Population (in Millions) ] INT
)
GO
INSERT INTO tblPopulation VALUES ( ' India ' , ' Delhi ' , ' East Delhi ' , 9 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Delhi ' , ' South Delhi ' , 8 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Delhi ' , ' North Delhi ' , 5.5 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Delhi ' , ' West Delhi ' , 7.5 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Karnataka ' , ' Bangalore ' , 9.5 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Karnataka ' , ' Belur ' , 2.5 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Karnataka ' , ' Manipal ' , 1.5 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Maharastra ' , ' Mumbai ' , 30 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Maharastra ' , ' Pune ' , 20 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Maharastra ' , ' Nagpur ' , 11 )
INSERT INTO tblPopulation VALUES ( ' India ' , ' Maharastra ' , ' Nashik ' , 6.5 )
GO
SELECT Country, [ State ] ,City,
SUM ( [ Population (in Millions) ] ) AS [ Population (in Millions) ]
FROM tblPopulation
GROUP BY Country, [ State ] ,City WITH ROLLUP
SQL SERVER – Introduction to Rollup Clause
[UPDATE 2010-9-1]另一种表现形式,把汇总行的信息改为:合计
CASE
WHEN ( GROUPING ( Country ) = 1 ) THEN N ' 合计 '
ELSE isnull ( Country , ' UNKNOWN ' )
END AS Country,
CASE
WHEN ( GROUPING ( [ State ] ) = 1 ) THEN N ' 合计 '
ELSE isnull ( [ State ] , ' UNKNOWN ' )
END AS [ State ] ,
CASE
WHEN ( GROUPING ( City ) = 1 ) THEN N ' 合计 '
ELSE isnull ( City , ' UNKNOWN ' )
END AS City ,
sum ( [ Population (in Millions) ] ) AS [ Population (in Millions) ]
FROM tblPopulation
GROUP BY Country , [ State ] , City WITH ROLLUP
最后结果:
Country State City Population (in Millions)
---------- ----------- ------------ ------------------------
India Delhi East Delhi 9
India Delhi North Delhi 5
India Delhi South Delhi 8
India Delhi West Delhi 7
India Delhi 合计 29
India Karnataka Bangalore 9
India Karnataka Belur 2
India Karnataka Manipal 1
India Karnataka 合计 12
India Maharastra Mumbai 30
India Maharastra Nagpur 11
India Maharastra Nashik 6
India Maharastra Pune 20
India Maharastra 合计 67
India 合计 合计 108
合计 合计 合计 108