如何在单SELECT语句中按多条件分组并横向展示统计结果

要实现横向展示各时间区间的统计结果，你可以用条件聚合（在聚合函数中嵌套CASE语句）来完成行转列，不需要用UNION。修改后的查询语句如下：

SELECT 
  p.category,
  COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 1 AND 5 THEN 1 END) AS `1-5`,
  COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 6 AND 9 THEN 1 END) AS `6-9`,
  COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 10 AND 15 THEN 1 END) AS `10-15`,
  COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) >=16 THEN 1 END) AS `16-20`
FROM product p  
GROUP BY p.category;

说明： #

• 每个COUNT(CASE ...)会单独统计对应时间区间的产品数量：当满足区间条件时返回1，否则返回NULL，COUNT会忽略NULL值，得到该区间的有效计数。
• GROUP BY只保留p.category，这样每个分类会生成一行数据，各区间的统计结果作为列横向展示。
• 区间列名用反引号`包裹，避免因为包含特殊字符（比如减号）导致语法错误。

如果需要处理区间内无数据的情况（显示0而非NULL），可以用IFNULL包裹COUNT：

SELECT 
  p.category,
  IFNULL(COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 1 AND 5 THEN 1 END), 0) AS `1-5`,
  IFNULL(COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 6 AND 9 THEN 1 END), 0) AS `6-9`,
  IFNULL(COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 10 AND 15 THEN 1 END), 0) AS `10-15`,
  IFNULL(COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) >=16 THEN 1 END), 0) AS `16-20`
FROM product p  
GROUP BY p.category;

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