MySQL带SUM(value)筛选的UNION查询及联合查询失效问题排查

首先，你的原查询无法正常运行的核心问题是UNION 结果的聚合筛选逻辑位置错误，另外还有几个细节需要调整，我来一步步拆解并修正：

原查询的主要问题 #

• GROUP BY 和 HAVING 直接放在 UNION 之后不符合SQL语法规则，聚合操作（比如分组求和）必须基于一个完整的结果集，你需要先把所有UNION合并后的结果包裹成子查询，再在外层进行分组和筛选。
• 使用 UNION 默认会自动去重，如果你的场景不需要去掉重复行（比如不同子查询可能返回相同物品，但你想保留），建议改用 UNION ALL，这样不仅执行效率更高，还能避免意外丢失数据。
• 原查询中每个子查询的 ORDER BY ... LIMIT 是合法的，但最后外层的 ORDER BY RAND() 是针对聚合后的分组结果，这可能和你想要返回具体物品的预期不符。

修正后的查询写法 #

场景1：筛选按weight分组后SUM(value)小于100的分组数据

如果你是想按weight分组，只保留分组内总价值小于100的组数据，可将UNION结果作为子查询，在外层做聚合筛选：

SELECT t.*, SUM(t.value) AS group_total_value
FROM (
    (SELECT * FROM `items` WHERE `name` LIKE '%SCAR%' AND `rarity` = 'Legendary' ORDER BY weight / RAND() DESC LIMIT 1)
    UNION ALL
    (SELECT * FROM `items` WHERE `rarity` = 'Legendary' ORDER BY weight / RAND() DESC LIMIT 3)
    UNION ALL
    (SELECT * FROM `items` WHERE `rarity` = 'Epic' ORDER BY weight / RAND() DESC LIMIT 1)
) AS t
GROUP BY t.weight, t.id, t.name, t.rarity, t.value  -- 需包含所有非聚合列，适配SQL模式要求
HAVING SUM(t.value) < 100
ORDER BY RAND();

场景2：确保选出的所有物品总价值小于100（返回这些物品）

如果你是想让这1+3+1=5个物品的总价值小于100，可使用窗口函数计算总价值后再筛选：

SELECT t.*
FROM (
    SELECT *, SUM(value) OVER () AS total_value
    FROM (
        (SELECT * FROM `items` WHERE `name` LIKE '%SCAR%' AND `rarity` = 'Legendary' ORDER BY weight / RAND() DESC LIMIT 1)
        UNION ALL
        (SELECT * FROM `items` WHERE `rarity` = 'Legendary' ORDER BY weight / RAND() DESC LIMIT 3)
        UNION ALL
        (SELECT * FROM `items` WHERE `rarity` = 'Epic' ORDER BY weight / RAND() DESC LIMIT 1)
    ) AS sub
) AS t
WHERE t.total_value < 100
ORDER BY RAND();

额外说明 #

• 如果你的SQL版本较低（比如MySQL 5.x）不支持窗口函数，可以用子查询计算总价值后再关联筛选；
• ORDER BY weight / RAND() 会每次生成随机结果，符合你的随机筛选需求，如果需要固定随机结果，可给RAND()传入固定种子值，比如RAND(123)。

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