SQL查询：统计各国家偏好游戏为NES的会员数量

需求 #

统计不同国家中，偏好游戏设置为NES的会员人数，结果按国家列展示对应计数（无符合条件的国家显示0）。

预期输出 #

COUNT(AUD)  COUNT(EUR)  COUNT(IND)  COUNT(LAO)  COUNT(USA)  COUNT(ZWE)  
    3           2           0           0       2           1

实现SQL语句 #

假设会员表名为members，包含country（国家）和preferred_game（偏好游戏）字段，可通过条件聚合实现需求：

SELECT
  COUNT(CASE WHEN country = 'AUD' AND preferred_game = 'NES' THEN 1 END) AS `COUNT(AUD)`,
  COUNT(CASE WHEN country = 'EUR' AND preferred_game = 'NES' THEN 1 END) AS `COUNT(EUR)`,
  COUNT(CASE WHEN country = 'IND' AND preferred_game = 'NES' THEN 1 END) AS `COUNT(IND)`,
  COUNT(CASE WHEN country = 'LAO' AND preferred_game = 'NES' THEN 1 END) AS `COUNT(LAO)`,
  COUNT(CASE WHEN country = 'USA' AND preferred_game = 'NES' THEN 1 END) AS `COUNT(USA)`,
  COUNT(CASE WHEN country = 'ZWE' AND preferred_game = 'NES' THEN 1 END) AS `COUNT(ZWE)`
FROM members;

也可使用SUM函数实现相同效果：

SELECT
  SUM(CASE WHEN country = 'AUD' AND preferred_game = 'NES' THEN 1 ELSE 0 END) AS `COUNT(AUD)`,
  SUM(CASE WHEN country = 'EUR' AND preferred_game = 'NES' THEN 1 ELSE 0 END) AS `COUNT(EUR)`,
  SUM(CASE WHEN country = 'IND' AND preferred_game = 'NES' THEN 1 ELSE 0 END) AS `COUNT(IND)`,
  SUM(CASE WHEN country = 'LAO' AND preferred_game = 'NES' THEN 1 ELSE 0 END) AS `COUNT(LAO)`,
  SUM(CASE WHEN country = 'USA' AND preferred_game = 'NES' THEN 1 ELSE 0 END) AS `COUNT(USA)`,
  SUM(CASE WHEN country = 'ZWE' AND preferred_game = 'NES' THEN 1 ELSE 0 END) AS `COUNT(ZWE)`
FROM members;

内容的提问来源于stack exchange，提问作者stella2343