我刚刚在网上的评论中找到了另一个答案:

对于几乎所有SQL中的中位数: SELECT x.val from data x, data y GROUP BY x.val 总和(符号(1-SIGN (y.val-x.val))) = (COUNT (*) + 1) / 2

确保列有良好的索引，并且索引用于筛选和排序。与解释计划核对。

select count(*) from table --find the number of rows

计算“中值”行号。可能使用:median_row = floor(count / 2)。

然后把它从列表中挑出来:

select val from table order by val asc limit median_row,1

这将返回您想要的值的一行。

这是我的办法。当然，你可以把它放到一个过程中:-)

SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`);

SET @median = CONCAT('SELECT `val` FROM `data` ORDER BY `val` LIMIT ', @median_counter, ', 1');

PREPARE median FROM @median;

EXECUTE median;

你可以避免变量@median_counter，如果你替换它:

SET @median = CONCAT( 'SELECT `val` FROM `data` ORDER BY `val` LIMIT ',
                      (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`),
                      ', 1'
                    );

PREPARE median FROM @median;

EXECUTE median;

如果这是MySQL，现在有窗口函数，你可以这样做(假设你想四舍五入到最接近的整数-否则只需将round替换为CEIL或FLOOR或其他什么)。下面的解决方案适用于表，无论表的行数是偶数还是奇数:

WITH CTE AS (
    SELECT val,
            ROW_NUMBER() OVER (ORDER BY val ASC) AS rn,
            COUNT(*) OVER () AS total_count
    FROM data
)
SELECT ROUND(AVG(val)) AS median
FROM CTE
WHERE
    rn BETWEEN
    total_count / 2.0 AND
    total_count / 2.0 + 1;

I think some of the more recent answers on this thread were already getting at this approach, but it also seemed like people were overthinking it, so consider this an improved version. Regardless of SQL flavor, there is no reason anyone should be writing a huge paragraph of code with multiple subqueries just to get the median in 2021. However, please note that the above query only works if you're asked to find the median for a continuous series. Of course, regardless of row number, sometimes people do make a distinction between what is referred to as the Discrete Median and what is referred to as the Interpolated Median for a continuous series.

如果你被要求为一个离散级数找到中位数，而表的行数是偶数，那么上面的解决方案就不适合你，你应该恢复使用其他解决方案之一，比如TheJacobTaylor的。

下面的第二个解决方案是对TheJacobTaylor的稍微修改的版本，其中我显式地声明了CROSS JOIN。这个方法也适用于行数为奇数的表，不管你是被要求求连续序列的中位数还是离散序列的中位数，但我特别会在被要求求离散序列的中位数时使用这个方法。否则，使用第一种解决方案。这样，您就永远不必考虑数据是包含“偶数”还是“奇数”个数的数据点。

SELECT x.val AS median
FROM data x
CROSS JOIN data y
GROUP BY x.val
HAVING SUM(SIGN(1 - SIGN(y.val - x.val))) = (COUNT(*) + 1) / 2;

最后，你可以在PostgreSQL中使用内置函数轻松做到这一点。这里有一个很好的解释，以及关于离散中位数和插值中位数的有效总结。

https://leafo.net/guides/postgresql-calculating-percentile.html#calculating-the-median

我发现接受的解决方案在我的MySQL安装上不起作用，返回一个空集，但这个查询在我测试的所有情况下都适用:

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5
LIMIT 1

通常，我们不仅需要为整个表计算Median，还需要为与ID相关的聚合计算Median。换句话说，计算表中每个ID的中位数，其中每个ID有许多记录。(良好的性能和工作在许多SQL +修复偶数和赔率的问题，更多关于不同的中值方法的性能https://sqlperformance.com/2012/08/t-sql-queries/median)

SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val, 
  COUNT(*) OVER (PARTITION BY our_id) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
  FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

希望能有所帮助

我没有将这个解决方案的性能与这里发布的其他答案进行比较，但我发现这个解决方案是最容易理解的，并且涵盖了计算中位数的全部数学公式。换句话说，这个解决方案对于偶数和奇数数据集足够健壮:

SELECT CASE 
-- odd-numbered data sets:
WHEN MOD(COUNT(*), 2) = 1 THEN (SELECT median.<value> AS median
FROM
(SELECT t1.<value>
  FROM (SELECT <value>, 
               ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
          FROM <data>) t1,
       (SELECT COUNT(*) AS num_records FROM <data>) t2
 WHERE t1.rownum =(t2.num_records) / 2) as median)
-- even-numbered data sets:
ELSE (select (low_bound.<value> + up_bound.<value>) / 2 AS median
FROM
(SELECT t1.<value>
  FROM (SELECT <value>, 
               ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
          FROM <data>) t1,
       (SELECT COUNT(*) AS num_records FROM <data>) t2
 WHERE t1.rownum =(t2.num_records - 1) / 2) as low_bound,
 (SELECT t1.<value>
  FROM (SELECT <value>, 
               ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
          FROM station) t1,
       (SELECT COUNT(*) AS num_records FROM data) t2
 WHERE t1.rownum =(t2.num_records + 1) / 2) as up_bound)
END
FROM <data>