MySQL UNION查询未达预期：按Channels.sort排序合并流与标记

你的SQL语句存在几个核心问题，直接导致结果不符合预期：

1. 错误的关联逻辑与字段输出 #

你原查询把Streams和Markers做了不必要的交叉关联，导致频道和标记错误配对（比如所有频道都和MEGA标记绑定），同时输出的字段结构和你期望的不匹配——你需要把频道名和标记title合并到同一个channel列，而不是拆分成channel和title两列。

2. 未处理Sort数组的顺序与元素类型区分 #

你没有将Channels.sort数组拆分成独立行，也没区分数组里的元素是标记（如m1、m2）还是频道ID（如1、2、11），更没保留数组的原始顺序，这直接导致结果无法按你想要的顺序输出。

正确的SQL实现方案 #

我们需要先拆分Channels.sort数组为单独行并记录顺序，再分别关联Streams和Markers表获取对应内容，最后按原始顺序排序：

WITH sorted_elements AS (
    -- 拆分Channels表的sort数组，记录每个元素的原始顺序
    SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(c.sort, CONCAT('$[', idx, ']'))) AS element,
        idx AS sort_position
    FROM channels c
    -- 生成数组索引，可根据实际数组长度调整数量
    JOIN (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS indices
    ON idx < JSON_LENGTH(c.sort)
    -- 只筛选目标bouquet的配置
    WHERE JSON_SEARCH(c.bouquet, 'one', '1') IS NOT NULL
)
-- 合并标记和频道结果，按原始顺序排序
SELECT 
    CASE 
        WHEN se.element LIKE 'm%' THEN m.id 
        ELSE s.id 
    END AS id,
    CASE 
        WHEN se.element LIKE 'm%' THEN m.title 
        ELSE s.channel 
    END AS channel
FROM sorted_elements se
-- 关联标记表（匹配标记类型元素）
LEFT JOIN markers m ON se.element = m.marker AND m.bouquet = 1
-- 关联频道表（匹配频道ID类型元素）
LEFT JOIN streams s ON se.element = CAST(s.id AS CHAR) AND JSON_SEARCH(s.bouquet, 'one', '1') IS NOT NULL
-- 过滤无效匹配项
WHERE m.id IS NOT NULL OR s.id IS NOT NULL
-- 严格按照sort数组的原始顺序输出
ORDER BY se.sort_position;

代码解释 #

1. CTE sorted_elements：

   • 通过生成索引的方式拆分sort数组为每行一个元素，同时记录元素在原数组中的位置sort_position（这是保证顺序的核心）。
   • 只筛选bouquet为1的配置，缩小处理范围。

2. 主查询：

   • 用CASE语句区分元素类型：以m开头的是标记，关联Markers表取title；否则是频道ID，关联Streams表取channel名称。
   • 用LEFT JOIN确保每个元素都能匹配对应数据，再通过WHERE过滤无效项。
   • 最后按sort_position排序，完全贴合Channels.sort数组的顺序。

执行该查询后，就能得到你期望的结果：

+----+-------------------+
| id | channel           |
+----+-------------------+
| 1  | MEGA              |
| 1  | USA_CH 1          |
| 2  | USA_CH 2          |
| 2  | XTRA              |
| 11 | USA_CH 3          |
+----+-------------------+

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