SQL多字段唯一值提取及对应数量求和实现求助

没问题，我来帮你搞定这个需求！

你当前的表是典型的“宽表”结构——把6组值分别存在独立的列里（比如Value1/value1qty到Value6/value6qty），这种结构没法直接做分组汇总，我们需要先把它转换成“长表”结构，再进行聚合操作。

步骤1：把多列数据拆成行数据 #

用UNION ALL把6组列分别拆成独立的行，让每个Value和对应的Quantity变成一条单独的记录：

SELECT Value1 AS value, value1qty AS quantity FROM your_table
UNION ALL
SELECT Value2 AS value, value2qty AS quantity FROM your_table
UNION ALL
SELECT Value3 AS value, value3qty AS quantity FROM your_table
UNION ALL
SELECT Value4 AS value, value4qty AS quantity FROM your_table
UNION ALL
SELECT Value5 AS value, value5qty AS quantity FROM your_table
UNION ALL
SELECT Value6 AS value, value6qty AS quantity FROM your_table

注意：把your_table替换成你实际的表名就行。UNION ALL会保留所有行（包括重复的value），正好满足我们后续汇总的需求。

步骤2：分组汇总唯一值的总数量 #

把上面的查询作为子查询，用GROUP BY按value分组，再用SUM()计算每个值对应的总数量：

SELECT value, SUM(quantity) AS total_quantity
FROM (
    SELECT Value1 AS value, value1qty AS quantity FROM your_table
    UNION ALL
    SELECT Value2 AS value, value2qty AS quantity FROM your_table
    UNION ALL
    SELECT Value3 AS value, value3qty AS quantity FROM your_table
    UNION ALL
    SELECT Value4 AS value, value4qty AS quantity FROM your_table
    UNION ALL
    SELECT Value5 AS value, value5qty AS quantity FROM your_table
    UNION ALL
    SELECT Value6 AS value, value6qty AS quantity FROM your_table
) AS transformed_data
WHERE value IS NOT NULL  -- 可选：如果部分Value字段可能为空，加上这句过滤空值
GROUP BY value
ORDER BY total_quantity DESC;  -- 可选：按总数量从高到低排序，方便查看结果

额外小提示 #

• 如果你的Value字段存在数据类型不一致的情况（比如数字和字符串混合），可以用CAST(value AS VARCHAR)统一类型后再分组，避免因类型差异导致的分组错误。
• 哪怕你的表中有多条原始记录，这个方法也完全适用——UNION ALL会把所有行的6组列都拆出来，再统一做汇总。

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