SQL:合并结果列

Question

SELECT Category, SUM (Volume) as Volume 
FROM Product 
GROUP BY Category;

上面的查询返回这个结果:

Category    Volume
-------------------
Oth          2
Tv Kids      4
{null}       1
Humour       3
Tv           5
Theatrical   13
Doc          6

我想将一些列合并为一个列，如下所示:

• Oth,{null}, 幽默, Doc as Others
• Tv Kids，Tv as TV
• 戏剧电影

所以我的结果看起来像:

Category    Volume
-------------------
Others       12
Tv           9
Film         13

我该怎么做？

Answer 1

这里需要一个 CASE，像这样:

SELECT 
  CASE 
    WHEN Category IN ('Oth','Humour','Doc') 
    OR Category IS NULL THEN 'Others'
    WHEN Category IN ('Tv Kids','Tv') THEN 'TV' 
    WHEN Category = 'Theatrical' THEN 'Film' 
  END as category , 
  SUM (Volume) as Volume 
from Product 
GROUP BY 
  CASE 
    WHEN Category IN ('Oth','Humour','Doc')
    OR Category IS NULL THEN 'Others'
    WHEN Category IN ('Tv Kids','Tv') THEN 'TV' 
    WHEN Category = 'Theatrical' THEN 'Film' 
  END;

Null 必须在 IN 列表之外处理，因为它是一个特殊值。