With Cube and With Rollup

表

ID     Englishname         score

1      tony                       90

2      tim                         10

3      daren                     20

查询语句

SELECT CASE WHEN (GROUPING(ID) = 1) THEN 'ALL'
       ELSE ISNULL(ID, 'UNKNOWN')
       END AS ID,
       CASE WHEN (GROUPING(Englishname) = 1) THEN 'ALL'
       ELSE ISNULL(Englishname, 'UNKNOWN')
       END AS Englishname,
       SUM(scores) AS scoreSum
FROM scores
GROUP BY ID, Englishname WITH Cube

 

查询结果

1 Tony 90
1 ALL 90
2 Tim 10
2 ALL 10
3 Daren 20
3 ALL 20
ALL ALL 120
ALL Daren 20
ALL Tim 10
ALL Tony 90

 

查询语句

SELECT CASE WHEN (GROUPING(ID) = 1) THEN 'ALL'
       ELSE ISNULL(ID, 'UNKNOWN')
       END AS ID,
       CASE WHEN (GROUPING(Englishname) = 1) THEN 'ALL'
       ELSE ISNULL(Englishname, 'UNKNOWN')
       END AS Englishname,
       SUM(scores) AS scoreSum
FROM scores
GROUP BY ID, Englishname WITH rollup

 

 

查询结果

1 Tony 90
1 ALL 90
2 Tim 10
2 ALL 10
3 Daren 20
3 ALL 20
ALL ALL 120