如何用外层SELECT的P.game替换子查询中的EXAMPLE常量？

我有如下查询语句：

SELECT DISTINCT P.game, 
(
SELECT AVG(dd.DUR) as median_val
FROM (
SELECT  S.val AS DUR, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM (SELECT TIMESTAMPDIFF(minute, M.date_start, M.date_finished) as val FROM matches M INNER JOIN tournaments T ON M.tournament_id=T.id
  WHERE M.tournament_id NOT IN (5,6) AND T.game = 'EXAMPLE'
  ORDER BY val) S, (SELECT @rownum:=0) r
) as dd
WHERE dd.row_number IN (FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2))
) AS duration
FROM matches M INNER JOIN tournaments P ON M.tournament_id=P.id WHERE P.id NOT IN (5,6) GROUP BY P.game

该查询可以运行，但由于T.game = 'EXAMPLE'是固定值，导致第二列的所有结果都相同。请问如何将子查询中的EXAMPLE常量替换为外层SELECT语句中的P.game值？

表结构及数据 #

tournaments表 #

CREATE TABLE tournaments (id INT PRIMARY KEY, game VARCHAR(64));
INSERT INTO tournaments (`id`, `game`) VALUES ('1', 'EXAMPLE'), ('2', 'TETRIS'), ('3', 'MARIO'), ('4', 'EXAMPLE'), ('5', 'TETRIS'), ('6', 'MARIO');

数据：

matches表 #

CREATE TABLE matches (id INT PRIMARY KEY, tournament_id INT, date_start DATETIME, date_finished DATETIME);
INSERT INTO matches (`id`, `tournament_id`, `date_start`, `date_finished`) VALUES ('1', '1', '2024-01-18 01:10:00', '2024-01-18 01:12:00'), ('2', '2', '2024-01-18 01:10:00', '2024-01-18 01:18:00'), ('3', '3', '2024-01-18 01:10:00', '2024-01-18 01:16:00'), ('4', '3', '2024-01-18 01:10:00', '2024-01-18 01:22:00'), ('5', '2', '2024-01-18 01:10:00', '2024-01-18 01:31:00'), ('6', '1', '2024-01-18 01:10:00', '2024-01-18 01:19:00'), ('7', '2', '2024-01-18 01:10:00', '2024-01-18 01:45:00'), ('8', '4', '2024-01-18 01:10:00', '2024-01-18 01:28:00'), ('9', '4', '2024-01-18 01:10:00', '2024-01-18 01:54:00'), ('10', '5', '2024-01-18 01:10:00', '2024-01-18 01:12:00'), ('11', '6', '2024-01-18 01:10:00', '2024-01-18 01:18:00'), ('12', '7', '2024-01-18 01:10:00', '2024-01-18 01:16:00'), ('13', '7', '2024-01-18 01:10:00', '2024-01-18 01:22:00'), ('14', '6', '2024-01-18 01:10:00', '2024-01-18 01:31:00'), ('15', '5', '2024-01-18 01:10:00', '2024-01-18 01:19:00'), ('16', '6', '2024-01-18 01:10:00', '2024-01-18 01:45:00'), ('17', '6', '2024-01-18 01:10:00', '2024-01-18 01:28:00'), ('18', '5', '2024-01-18 01:10:00', '2024-01-18 01:54:00');

数据：

期望结果 #

按game分组，第二列为对应game的比赛时长中位数（TIMESTAMPDIFF(minute, M.date_start, M.date_finished)的中位数）：

要让子查询引用外层的P.game，需将子查询改为关联子查询，同时确保每个分组的行号变量重新初始化，修改后的查询语句如下：

SELECT 
    P.game,
    (
        SELECT AVG(dd.DUR) AS median_val
        FROM (
            SELECT 
                S.val AS DUR,
                @rownum := @rownum + 1 AS `row_number`,
                @total_rows := @rownum
            FROM (
                SELECT TIMESTAMPDIFF(minute, M.date_start, M.date_finished) AS val
                FROM matches M
                INNER JOIN tournaments T ON M.tournament_id = T.id
                WHERE M.tournament_id NOT IN (5,6) 
                  AND T.game = P.game  -- 替换为外层的P.game
                ORDER BY val
            ) S,
            (SELECT @rownum := 0) r  -- 每个分组重新初始化行号变量
        ) dd
        WHERE dd.row_number IN (FLOOR((@total_rows + 1)/2), FLOOR((@total_rows + 2)/2))
    ) AS duration
FROM matches M
INNER JOIN tournaments P ON M.tournament_id = P.id
WHERE P.id NOT IN (5,6)
GROUP BY P.game;

关键说明 #

1. 将子查询中的T.game = 'EXAMPLE'替换为T.game = P.game，使子查询随外层分组的game值筛选对应数据。
2. 每个子查询执行时通过(SELECT @rownum := 0) r重新初始化行号变量，避免不同分组的行号混乱。
3. 原查询中的DISTINCT可删除，GROUP BY P.game已保证game的唯一性。

执行该查询后，即可得到每个game对应的比赛时长中位数，与期望结果一致。

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