MySQL在基于性别和生日的选择案例中显示空值

Question

我有基于出生和性别的 CASE 条件的 SQL 语法。下面是我使用的示例查询

SELECT   Age,  SUM(gender = 'M') M,  SUM(gender = 'F') F,  COUNT(*) TotalPerson FROM  (SELECT     CASE      WHEN FLOOR(DATEDIFF(CURDATE(), birdth) / 365)  60 THEN '60 Above'     END Age,    gender   FROM    society) ageList GROUP BY Age 

结果变成这样:

Age         | M     | F     | TotalPerson--------------------------------19 - 25     | 1     | 0     | 119 - 25     | 1     | 0     | 126 - 45     | 1     | 2     | 346 - 60     | 0     | 1     | 160 Above    | 0     | 2     | 2

这里是我的 sqlfiddle 的完整语法: http://sqlfiddle.com/#!9/e8c12a/2

但我真正需要的结果是还显示空值的年龄。
像这样的例子:

Age         | M     | F     | TotalPerson--------------------------------0 - 5       | 0     | 0     | 06 - 10      | 0     | 0     | 011 - 18     | 0     | 0     | 019 - 25     | 1     | 0     | 119 - 25     | 1     | 0     | 126 - 45     | 1     | 2     | 346 - 60     | 0     | 1     | 160 Above    | 0     | 2     | 2

Answer 1

你可以加入社会:

SELECT 
  Age,
  COALESCE(SUM(gender = 'M'), 0) M,
  COALESCE(SUM(gender = 'F'), 0) F,
  COALESCE(SUM(gender = 'M'), 0) + COALESCE(SUM(gender = 'F'), 0) TotalPerson 
FROM
  (
    SELECT '0 - 5' AS Age, 0 AS age1, 6 AS age2 UNION
    SELECT '6 - 10', 6, 10 UNION
    SELECT '11 - 18', 11, 18 UNION
    SELECT '19 - 25', 19, 25 UNION
    SELECT '26 - 45', 26, 45 UNION
    SELECT '46 - 60', 46, 60 UNION
    SELECT '60 Above', 60, 1000
  ) ages
  LEFT JOIN society ON FLOOR(DATEDIFF(CURDATE(), birdth) / 365) BETWEEN age1 AND age2
GROUP BY Age
ORDER BY age1

关于 SQL Fiddle 的示例