MySQL子查询中如何正确选两列？报操作数需含1个值错误

你遇到的这个报错核心原因很明确：在SELECT的字段列表里，你嵌套的子查询返回了两列数据（median_val1和median_val2），但MySQL要求SELECT列表中的每个子查询必须只能返回单个值，所以数据库就抛出了这个错误。

下面给你两种针对性的解决方案，你可以根据自己的MySQL版本和需求选择：

方案一：拆分子查询（适合所有MySQL版本） #

把原来返回两列的子查询拆成两个独立的子查询，每个子查询只返回一个字段的中位数，这样就符合MySQL的要求了。注意要给每个子查询使用独立的变量，避免变量互相干扰：

SELECT 
    a.date_insert AS date,
    HOUR(a.date_insert) AS hour,
    AVG(a.spood) AS avg_spood,
    AVG(a.factor) AS avg_factor,
    -- 单独计算spood的中位数
    (SELECT AVG(dd.spood)
     FROM (
         SELECT d.spood, @rownum1:=@rownum1+1 as row_number, @total_rows1:=@rownum1
         FROM traf d, (SELECT @rownum1:=0) r
         WHERE d.spood IS NOT NULL
         ORDER BY d.spood
     ) as dd
     WHERE dd.row_number IN (FLOOR((@total_rows1+1)/2), FLOOR((@total_rows1+2)/2))
    ) AS median_spood,
    -- 单独计算factor的中位数
    (SELECT AVG(dd.factor)
     FROM (
         SELECT d.factor, @rownum2:=@rownum2+1 as row_number, @total_rows2:=@rownum2
         FROM traf d, (SELECT @rownum2:=0) r
         WHERE d.factor IS NOT NULL
         ORDER BY d.factor
     ) as dd
     WHERE dd.row_number IN (FLOOR((@total_rows2+1)/2), FLOOR((@total_rows2+2)/2))
    ) AS median_factor
FROM traf a 
INNER JOIN mycolumn b ON a.ref_id = b.ref_id 
WHERE value_3 > 100 
GROUP BY date, hour;

方案二：用JOIN整合中位数计算（性能更优，适合所有版本） #

如果数据量较大，拆分子查询会扫描两次表，改用CROSS JOIN把中位数计算逻辑整合到一次扫描里，性能会更好：

SELECT 
    a.date_insert AS date,
    HOUR(a.date_insert) AS hour,
    AVG(a.spood) AS avg_spood,
    AVG(a.factor) AS avg_factor,
    median_calc.median_spood,
    median_calc.median_factor
FROM traf a 
INNER JOIN mycolumn b ON a.ref_id = b.ref_id 
-- 关联一次性计算两个中位数的子查询
CROSS JOIN (
    SELECT 
        AVG(CASE WHEN row_num_spood IN (FLOOR((total_spood+1)/2), FLOOR((total_spood+2)/2)) THEN spood END) AS median_spood,
        AVG(CASE WHEN row_num_factor IN (FLOOR((total_factor+1)/2), FLOOR((total_factor+2)/2)) THEN factor END) AS median_factor
    FROM (
        SELECT 
            spood,
            factor,
            @rownum_spood:=@rownum_spood+1 AS row_num_spood,
            @total_spood:=@rownum_spood AS total_spood,
            @rownum_factor:=@rownum_factor+1 AS row_num_factor,
            @total_factor:=@rownum_factor AS total_factor
        FROM traf d, (SELECT @rownum_spood:=0, @rownum_factor:=0) r
        WHERE d.spood IS NOT NULL AND d.factor IS NOT NULL
        ORDER BY spood, factor -- 可根据需求调整排序逻辑
    ) AS ranked_data
) AS median_calc
WHERE value_3 > 100 
GROUP BY date, hour;

额外说明：如果需要分组内的中位数 #

如果你想要的不是整个表的中位数，而是按date和hour分组后，每个分组内的spood和factor中位数，在MySQL 8.0及以上版本可以用窗口函数实现，逻辑更清晰：

WITH grouped_data AS (
    SELECT 
        date_insert AS date,
        HOUR(date_insert) AS hour,
        spood,
        factor,
        -- 按分组给spood排序并编号
        ROW_NUMBER() OVER(PARTITION BY date_insert, HOUR(date_insert) ORDER BY spood) AS row_num_spood,
        -- 获取分组内的总行数
        COUNT(*) OVER(PARTITION BY date_insert, HOUR(date_insert)) AS total_spood,
        -- 按分组给factor排序并编号
        ROW_NUMBER() OVER(PARTITION BY date_insert, HOUR(date_insert) ORDER BY factor) AS row_num_factor,
        COUNT(*) OVER(PARTITION BY date_insert, HOUR(date_insert)) AS total_factor
    FROM traf a 
    INNER JOIN mycolumn b ON a.ref_id = b.ref_id 
    WHERE value_3 > 100 
        AND spood IS NOT NULL 
        AND factor IS NOT NULL
)
SELECT 
    date,
    hour,
    AVG(spood) AS avg_spood,
    AVG(factor) AS avg_factor,
    AVG(CASE WHEN row_num_spood IN (FLOOR((total_spood+1)/2), FLOOR((total_spood+2)/2)) THEN spood END) AS median_spood,
    AVG(CASE WHEN row_num_factor IN (FLOOR((total_factor+1)/2), FLOOR((total_factor+2)/2)) THEN factor END) AS median_factor
FROM grouped_data
GROUP BY date, hour;

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