mysql 按一次选择中的多个标准分组[重复]

ijxebb2r 于 2023-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(157)

此问题已在此处有答案：

How can I return pivot table output in MySQL?（10个答案）
14天前关闭
Expected使用group by和case，我能够获得一个时间间隔内每个产品的统计结果。但是我希望每个间隔的结果是水平的（上图），注意使用下面的查询我们可以垂直地得到结果：

SELECT 
  p.category,
  (CASE
    WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 1 AND 5 THEN '1-5'
    WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 6 AND 9 THEN '6-9'
    WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 10 AND 15 THEN '10-15'
    ELSE '16-20'
  END) as Interval,
  COUNT(*) AS totalNbr
FROM product p  
GROUP BY p.category, Interval

我试过使用联盟，但它还没有工作。

mysql

来源：https://stackoverflow.com/questions/76415114/group-by-multiple-criteria-in-one-select

1条答案

按热度按时间

2ekbmq321#

使用条件聚合。在MySQL中：

SELECT category,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) BETWEEN  1 AND  5) AS diff_1_5,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) BETWEEN  6 AND  9) AS diff_6_9,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) BETWEEN 10 AND 15) AS diff_10_15,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) >= 16)             AS diff_16_plus
FROM product
GROUP BY category

赞(0）回复(0）举报 2023-06-21

我来回答

mysql 按一次选择中的多个标准分组[重复]

1条答案

相关问题

热门标签

最新问答