有這樣一種情況
表1:
字段1?? 字段2?? 字段3
?A?????? 組1????? 20??
?B?????? 組1????? 30
?C?????? 組2????? 40
?D?????? 組1????? 50
?E?????? NULL???? 60
?F?????? 組2????? 70
?G?????? NULL???? 90
現在要實現對組的匯總 非組的也要匯總
結果
?ALL???? 組1????? 100
?ALL???? 組2????? 110
?E?????? NULL???? 60
?G?????? NULL???? 90
解答
SELECT
字段1 = CASE WHEN 字段2 IS NOT NULL THEN 'ALL' ELSE 字段1 END,
字段2,
字段3 = SUM(字段3)
FROM 表1
GROUP BY
CASE WHEN 字段2 IS NOT NULL THEN 'ALL' ELSE 字段1 END,
字段2