如何合并两个结构与记录不同的MySQL SELECT查询结果为JSON格式？

看起来你想把两个独立聚合的SQL查询结果合并成一个包含两个JSON字段的对象，你的思路方向是对的，但原写法里有几个需要修正的地方，我来帮你理清楚：

原写法的核心问题 #

1. 不必要关联主表：两个子查询已经是聚合后的单条结果，再关联calendar主表会导致结果重复，而且主查询的WHERE条件（周18）和第一个子查询的条件（周19）冲突，可能导致Event字段为空或结果不符合预期。
2. 关联逻辑错误：原写法里的ON y.idrow = idrow没有指定主表的idrow（主表根本没有这个字段），会引发语法错误。

正确的解决方案 #

因为两个子查询各自都只返回一行聚合结果，最直接的方式是用**交叉连接（CROSS JOIN）**或者基于虚拟idrow的等值连接，把两个单条结果合并成一行，再用json_object包装成你想要的结构。

方法1：使用CROSS JOIN（推荐）

这种方式最直观，因为两个子查询都是单条结果，交叉连接会直接把它们合并成一行：

SELECT json_object('Event', y.Event, 'opt', z.opt) AS Pippo
FROM (
    -- 第一个子查询：生成Event的JSON数组
    SELECT GROUP_CONCAT(json_object('Title', Title, 'Note', Note)) AS Event 
    FROM ( 
        SELECT 
            CASE WHEN tb_odl.id <> '44' AND tb_odl.id <> '64' THEN Odl_Desc END AS Title, 
            CASE WHEN tb_odl.id <> '44' AND tb_odl.id <> '64' THEN calendar.Notes END AS Note 
        FROM calendar 
        INNER JOIN tb_odl ON calendar.id_odl = tb_odl.id 
        INNER JOIN tb_user ON calendar.id_user = tb_user.id 
        WHERE WEEK(calendar.start, 1) = 19 AND YEAR(calendar.start) = 2021 
    ) sub1
) y
CROSS JOIN (
    -- 第二个子查询：生成opt的JSON数组
    SELECT GROUP_CONCAT(json_object('rep', rep, 'temp', temp, 'notte', notte)) AS opt 
    FROM ( 
        SELECT 
            CASE WHEN tb_odl.id = '44' THEN "yes" ELSE "no" END AS rep, 
            CASE WHEN tb_odl.id = '64' THEN "yes" ELSE "no" END AS temp, 
            CASE WHEN tb_odl.Icona = 'far fa-moon' THEN "yes" ELSE "no" END AS notte 
        FROM calendar 
        INNER JOIN tb_odl ON calendar.id_odl = tb_odl.id 
        INNER JOIN tb_user ON calendar.id_user = tb_user.id 
        WHERE WEEK(calendar.start, 1) = 18 AND YEAR(calendar.start) = 2021
    ) sub2
) z;

方法2：基于虚拟idrow的等值连接

如果你更倾向于用你最初的idrow思路，可以让两个子查询都返回相同的虚拟id，再通过这个id关联，效果和CROSS JOIN一致：

SELECT json_object('Event', y.Event, 'opt', z.opt) AS Pippo
FROM (
    SELECT 1 AS idrow, GROUP_CONCAT(json_object('Title', Title, 'Note', Note)) AS Event 
    FROM ( 
        SELECT 
            CASE WHEN tb_odl.id <> '44' AND tb_odl.id <> '64' THEN Odl_Desc END AS Title, 
            CASE WHEN tb_odl.id <> '44' AND tb_odl.id <> '64' THEN calendar.Notes END AS Note 
        FROM calendar 
        INNER JOIN tb_odl ON calendar.id_odl = tb_odl.id 
        INNER JOIN tb_user ON calendar.id_user = tb_user.id 
        WHERE WEEK(calendar.start, 1) = 19 AND YEAR(calendar.start) = 2021 
    ) sub1
) y
INNER JOIN (
    SELECT 1 AS idrow, GROUP_CONCAT(json_object('rep', rep, 'temp', temp, 'notte', notte)) AS opt 
    FROM ( 
        SELECT 
            CASE WHEN tb_odl.id = '44' THEN "yes" ELSE "no" END AS rep, 
            CASE WHEN tb_odl.id = '64' THEN "yes" ELSE "no" END AS temp, 
            CASE WHEN tb_odl.Icona = 'far fa-moon' THEN "yes" ELSE "no" END AS notte 
        FROM calendar 
        INNER JOIN tb_odl ON calendar.id_odl = tb_odl.id 
        INNER JOIN tb_user ON calendar.id_user = tb_user.id 
        WHERE WEEK(calendar.start, 1) = 18 AND YEAR(calendar.start) = 2021
    ) sub2
) z ON y.idrow = z.idrow;

额外提示 #

如果其中某个子查询可能没有符合条件的数据（返回空结果），可以把INNER JOIN换成LEFT JOIN，这样即使其中一个字段为空，另一个字段的结果依然能正常显示。

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