如何简化MySQL查询实现按月份统计不同尺码制服总量？

核心思路：宽表转窄表+一次分组统计 #

你的表属于典型的宽表结构（每个制服类型+尺码单独作为字段），之前逐个查询的方式效率低且繁琐。可以通过UNION ALL将宽表转成「制服类型-尺码-数量」的窄表结构，再一次性完成统计，只需要执行一次SQL查询。

具体SQL示例 #

假设你的表字段为RegID, Name, Month, Tshirt_XS, Tshirt_S, Tshirt_M, Jacket_XS, Jacket_S, Pants_XS, Pants_M（以此类推其他尺码），查询6月数据的SQL如下：

-- 先过滤出目标月份的数据，避免重复扫描全表
WITH FilteredData AS (
    SELECT * FROM TeamUniform WHERE Month = 6
)
SELECT 
    'Tshirt' AS uniform_type,
    'XS' AS size,
    SUM(Tshirt_XS) AS total_quantity
FROM FilteredData
UNION ALL
SELECT 
    'Tshirt' AS uniform_type,
    'S' AS size,
    SUM(Tshirt_S) AS total_quantity
FROM FilteredData
UNION ALL
SELECT 
    'Tshirt' AS uniform_type,
    'M' AS size,
    SUM(Tshirt_M) AS total_quantity
FROM FilteredData
UNION ALL
SELECT 
    'Jacket' AS uniform_type,
    'XS' AS size,
    SUM(Jacket_XS) AS total_quantity
FROM FilteredData
UNION ALL
SELECT 
    'Jacket' AS uniform_type,
    'S' AS size,
    SUM(Jacket_S) AS total_quantity
FROM FilteredData
UNION ALL
SELECT 
    'Pants' AS uniform_type,
    'XS' AS size,
    SUM(Pants_XS) AS total_quantity
FROM FilteredData
UNION ALL
SELECT 
    'Pants' AS uniform_type,
    'M' AS size,
    SUM(Pants_M) AS total_quantity
FROM FilteredData
-- 按制服类型、尺码排序，方便展示
ORDER BY uniform_type, size;

兼容性说明 #

如果你的MySQL版本低于8.0（不支持CTE），可以把过滤逻辑直接放到每个子查询里，或者用临时表替代：

SELECT 
    'Tshirt' AS uniform_type,
    'XS' AS size,
    SUM(Tshirt_XS) AS total_quantity
FROM TeamUniform WHERE Month = 6
UNION ALL
-- 后续子查询重复WHERE条件即可，省略部分代码...
ORDER BY uniform_type, size;

结果展示简化 #

执行上述查询后，会直接得到结构化的汇总结果：

后端代码只需要读取这一组结果，循环生成表格即可，不用再处理多个查询结果的拼接逻辑，大幅简化展示代码。

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