MySQL按日期统计不同严重级别值的出现次数

看起来你需要条件聚合来实现按日期分组统计每个严重级别的总次数，而不是统计不同级别的数量。我会针对你的两种期望输出分别给出SQL写法，同时也会处理文字类型级别（High/Medium/Low）的转换问题。

一、数字级别（1-5）的统计 #

期望输出1：日期为行，级别为列 #

这个需求可以通过CASE语句结合SUM函数实现，对每个级别单独计算该日期下的总次数：

SELECT 
    `DATE`,
    SUM(CASE WHEN LEVEL = 1 THEN COUNT ELSE 0 END) AS `1`,
    SUM(CASE WHEN LEVEL = 2 THEN COUNT ELSE 0 END) AS `2`,
    SUM(CASE WHEN LEVEL = 3 THEN COUNT ELSE 0 END) AS `3`,
    SUM(CASE WHEN LEVEL = 4 THEN COUNT ELSE 0 END) AS `4`,
    SUM(CASE WHEN LEVEL = 5 THEN COUNT ELSE 0 END) AS `5`
FROM myDatabase
GROUP BY `DATE`
ORDER BY `DATE`;

说明：

• 对每一行数据，判断它的LEVEL属于1-5中的哪一个，然后累加对应的COUNT值；如果不属于当前级别，就加0。
• 按DATE分组后，每个级别列就会显示该日期下对应级别的总次数，没有数据的级别会显示0。

期望输出2：级别为行，日期为列 #

如果日期是固定的，可以用静态的CASE语句实现；如果日期是动态变化的，推荐用动态SQL自动生成日期列。

静态写法（适用于已知日期的场景）

SELECT 
    LEVEL AS `Level`,
    SUM(CASE WHEN `DATE` = '05/11/2018' THEN COUNT ELSE 0 END) AS `05/11/2018`,
    SUM(CASE WHEN `DATE` = '05/12/2018' THEN COUNT ELSE 0 END) AS `05/12/2018`,
    SUM(CASE WHEN `DATE` = '05/13/2018' THEN COUNT ELSE 0 END) AS `05/13/2018`
FROM myDatabase
GROUP BY LEVEL
ORDER BY LEVEL;

动态写法（适用于日期不固定的场景，MySQL为例）

自动查询所有存在的日期并生成对应列：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN `DATE` = ''', `DATE`, ''' THEN COUNT ELSE 0 END) AS `', `DATE`, '`'
    )
  ) INTO @sql
FROM myDatabase;

SET @sql = CONCAT('SELECT LEVEL AS `Level`, ', @sql, ' FROM myDatabase GROUP BY LEVEL ORDER BY LEVEL');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

二、文字级别（High/Medium/Low）转数字统计 #

首先需要定义文字级别到数字级别的映射关系（比如High→5、Medium→3、Low→1，你可以根据实际需求调整），然后先转换级别再进行聚合。

示例：文字级别转数字后实现期望输出1 #

SELECT 
    `DATE`,
    SUM(CASE WHEN mapped_level = 1 THEN COUNT ELSE 0 END) AS `1`,
    SUM(CASE WHEN mapped_level = 2 THEN COUNT ELSE 0 END) AS `2`,
    SUM(CASE WHEN mapped_level = 3 THEN COUNT ELSE 0 END) AS `3`,
    SUM(CASE WHEN mapped_level = 4 THEN COUNT ELSE 0 END) AS `4`,
    SUM(CASE WHEN mapped_level = 5 THEN COUNT ELSE 0 END) AS `5`
FROM (
    SELECT 
        `DATE`,
        COUNT,
        CASE level_text
            WHEN 'Low' THEN 1
            WHEN 'Medium' THEN 3
            WHEN 'High' THEN 5
            ELSE NULL -- 处理未知级别，也可以设为0
        END AS mapped_level
    FROM text_level_table -- 你的文字级别表名
) AS mapped_data
GROUP BY `DATE`
ORDER BY `DATE`;

为什么你的原SQL不对？ #

你之前的SQL用了COUNT(DISTINCT LEVEL)，这个函数的作用是统计每个日期下有多少种不同的级别，而不是每个级别的总次数。比如05/11/2018有级别3和5，所以会返回2，这和你需要的每个级别累加次数的需求不符，所以需要用SUM结合CASE的条件聚合来实现。

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