按部门分组统计不同区域、语言信件数量的MySQL查询需求

需求说明 #

• 数据表tblltr记录了不同部门向A、B、C区域发送的印地语（Hindi）、英语（English）信件信息
• 需要按部门分组，统计每个部门向各区域发送的不同语言信件数量
• 当前查询仅能统计全量汇总数据，无法按部门拆分

当前汇总查询语句 #

select count(LtrNo) from tblltr where REGION="A" and Language="Hindi" 
select count(LtrNo) from tblltr where REGION="A"and Language="English"
select count(LtrNo) from tblltr where REGION="B" and Language="Hindi"
select count(LtrNo) from tblltr where REGION="B" and Language="English"
select count(LtrNo) from tblltr where REGION="C" and Language="Hindi"
select count(LtrNo) from tblltr where REGION="C" and Language="English" 

实现按部门分组的统计查询 #

使用GROUP BY按部门分组，结合条件聚合函数生成各区域-语言的统计列：

SELECT 
    Department,
    -- 区域A各语言信件数
    COUNT(CASE WHEN REGION = 'A' AND Language = 'Hindi' THEN LtrNo END) AS A_Hindi,
    COUNT(CASE WHEN REGION = 'A' AND Language = 'English' THEN LtrNo END) AS A_English,
    -- 区域B各语言信件数
    COUNT(CASE WHEN REGION = 'B' AND Language = 'Hindi' THEN LtrNo END) AS B_Hindi,
    COUNT(CASE WHEN REGION = 'B' AND Language = 'English' THEN LtrNo END) AS B_English,
    -- 区域C各语言信件数
    COUNT(CASE WHEN REGION = 'C' AND Language = 'Hindi' THEN LtrNo END) AS C_Hindi,
    COUNT(CASE WHEN REGION = 'C' AND Language = 'English' THEN LtrNo END) AS C_English
FROM tblltr
GROUP BY Department
ORDER BY Department;

逻辑说明 #

• GROUP BY Department：将数据按部门分组，确保每个部门作为一行结果
• CASE语句：筛选出对应区域和语言的记录，不满足条件时返回NULL
• COUNT()：自动忽略NULL值，统计符合条件的信件数量

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