如何结合关联表查询与中位数计算获取TABLE1每条记录的中位数

嘿，这个需求我熟！要给TABLE1里每条记录匹配上对应TABLE2分数的中位数，我们得把关联查询和分组中位数计算结合起来，分两种场景给你写解决方案：

这个版本支持窗口函数，写法更简洁易维护：

SELECT 
    t1.id,
    t1.name, -- 这里替换成你实际需要的TABLE1字段，比如name、create_time等
    AVG(t2.scores) AS median_score
FROM 
    TABLE1 t1
LEFT JOIN (
    SELECT 
        table1_id,
        scores,
        -- 给每个TABLE1分组内的分数排序并编号
        ROW_NUMBER() OVER (PARTITION BY table1_id ORDER BY scores) AS row_num,
        -- 计算每个TABLE1分组对应的分数总条数
        COUNT(*) OVER (PARTITION BY table1_id) AS total_rows
    FROM TABLE2
) t2 ON t1.id = t2.table1_id
WHERE 
    -- 筛选出每个分组中处于中位数位置的行：奇数条取中间1条，偶数条取中间2条
    t2.row_num IN (FLOOR((total_rows + 1)/2), CEIL((total_rows + 1)/2))
GROUP BY 
    t1.id, t1.name; -- 这里要和SELECT里的TABLE1字段完全对应

逻辑说明： #

1. 子查询里用PARTITION BY table1_id把TABLE2的记录按对应TABLE1的ID分组，给每组内的分数排序后编号，同时算出每组的总条数。
2. 外层通过LEFT JOIN关联TABLE1，筛选出每组里行号符合中位数位置的记录，最后分组取平均得到中位数（偶数条时自动取中间两个数的平均值）。

如果你的MySQL版本不支持窗口函数，可以用变量来实现分组计数：

-- 初始化变量：跟踪上一个分组的ID，以及当前分组的行号
SET @prev_table1_id := NULL;
SET @rowindex := -1;

SELECT 
    t1.id,
    t1.name, -- 替换成你需要的TABLE1字段
    AVG(g.scores) AS median_score
FROM 
    TABLE1 t1
LEFT JOIN (
    SELECT 
        table1_id,
        scores,
        -- 分组切换时重置行号，同一分组内递增行号
        @rowindex := CASE 
            WHEN @prev_table1_id = table1_id THEN @rowindex + 1 
            ELSE 0 
        END AS rowindex,
        @prev_table1_id := table1_id AS dummy,
        -- 先预计算每个分组的总条数
        (SELECT COUNT(*) FROM TABLE2 WHERE table1_id = t.table1_id) AS total_rows
    FROM TABLE2 t
    ORDER BY table1_id, scores
) g ON t1.id = g.table1_id
WHERE 
    g.rowindex IN (FLOOR((g.total_rows - 1)/2), CEIL((g.total_rows - 1)/2))
GROUP BY 
    t1.id, t1.name;

逻辑说明： #

1. 用@prev_table1_id跟踪当前处理的分组ID，当分组变化时把行号@rowindex重置为0，保证每个分组内的行号从0开始计数。
2. 子查询里通过嵌套查询提前算出每个分组的总条数，再筛选出中位数位置的行，最后取平均得到结果。

额外注意： #

• 如果某个TABLE1记录在TABLE2里没有对应分数，LEFT JOIN会保留这条记录，中位数会返回NULL，你可以用COALESCE(AVG(g.scores), 0)把NULL替换成0，根据实际需求调整。
• 记得把代码里的name替换成你实际需要从TABLE1获取的字段，GROUP BY子句必须和SELECT里的TABLE1字段完全对应（避免ONLY_FULL_GROUP_BY模式报错）。

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