学步园

CREATE TABLE tblPopulation (
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]另一种表现形式，把汇总行的信息改为：合计

代码
SELECT 
    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