SQL多子查询返回多行报错#1242：竞赛投票JSON聚合查询问题

你遇到的#1242 - Subquery returns more than 1 row错误，核心原因是：你放在SELECT列表里的两个子查询，各自按Edition分组后都会返回多行结果（每届竞赛对应一行），但MySQL要求SELECT列表中的子查询必须返回单行单列的值，无法直接接收多行结果，所以触发了报错。

正确的解决方案 #

我们需要把投票者和被评分用户的JSON数组结果，按Edition字段关联起来，让每一行对应一届竞赛的两组数据。这里推荐用CTE（公共表表达式）来拆分逻辑，更清晰易读：

WITH VoterData AS (
    SELECT 
        Edition,
        JSON_ARRAYAGG(JSON_OBJECT("Nickname", Users.Nickname, "Id", Votes.VoterId)) AS Voters
    FROM (
        -- 先去重，避免同一投票者在一届中多次出现在数组里
        SELECT DISTINCT Votes.VoterId, Votes.Edition
        FROM Votes
    ) AS DistinctVoters
    JOIN Users ON Users.Id = DistinctVoters.VoterId
    GROUP BY Edition
),
RatedUserData AS (
    SELECT 
        Edition,
        JSON_ARRAYAGG(JSON_OBJECT("Nickname", Users.Nickname, "Id", Votes.RatedUserId)) AS RatedUsers
    FROM (
        -- 同样先去重被评分用户
        SELECT DISTINCT Votes.RatedUserId, Votes.Edition
        FROM Votes
    ) AS DistinctRatedUsers
    JOIN Users ON Users.Id = DistinctRatedUsers.RatedUserId
    GROUP BY Edition
)
-- 最后按届数关联两个数据集
SELECT 
    v.Edition,
    v.Voters,
    ru.RatedUsers
FROM VoterData v
JOIN RatedUserData ru ON v.Edition = ru.Edition;

如果你的MySQL版本低于8.0（不支持CTE），可以改用子查询作为临时表的写法：

SELECT 
    v.Edition,
    v.Voters,
    ru.RatedUsers
FROM (
    SELECT 
        Edition,
        JSON_ARRAYAGG(JSON_OBJECT("Nickname", Users.Nickname, "Id", Votes.VoterId)) AS Voters
    FROM (
        SELECT DISTINCT Votes.VoterId, Votes.Edition
        FROM Votes
    ) AS DistinctVoters
    JOIN Users ON Users.Id = DistinctVoters.VoterId
    GROUP BY Edition
) v
JOIN (
    SELECT 
        Edition,
        JSON_ARRAYAGG(JSON_OBJECT("Nickname", Users.Nickname, "Id", Votes.RatedUserId)) AS RatedUsers
    FROM (
        SELECT DISTINCT Votes.RatedUserId, Votes.Edition
        FROM Votes
    ) AS DistinctRatedUsers
    JOIN Users ON Users.Id = DistinctRatedUsers.RatedUserId
    GROUP BY Edition
) ru ON v.Edition = ru.Edition;

为什么原来的查询会报错 #

你原来的写法中，每个子查询都是独立按Edition分组，返回的是多行结果（比如有3届竞赛就返回3行）。但主查询的SELECT列表里，每个位置只能放一个单一值，没法直接接收多行数据。通过将两个子查询的结果按Edition关联，我们就能让每一行对应一届竞赛的投票者和被评分用户数组，完美解决这个问题。

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