我下面提出的解决方案只需要一个查询，而不需要创建表、变量甚至子查询。 另外，它可以让你在组-查询(这是我需要的!)

SELECT `columnA`, 
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`columnB` ORDER BY `columnB`), ',', CEILING((COUNT(`columnB`)/2))), ',', -1) medianOfColumnB
FROM `tableC`
-- some where clause if you want
GROUP BY `columnA`;

它之所以能够工作，是因为巧妙地使用了group_concat和substring_index。

但是，为了允许大的group_concat，必须将group_concat_max_len设置为一个更高的值(默认为1024字符)。 你可以这样设置(对于当前的sql会话):

SET SESSION group_concat_max_len = 10000; 
-- up to 4294967295 in 32-bits platform.

有关group_concat_max_len的更多信息:https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

不幸的是，无论是TheJacobTaylor还是velcrow的答案都不会返回当前版本MySQL的准确结果。

从上面来看，魔术贴的答案是接近的，但它不能正确计算具有偶数行数的结果集。中位数定义为1)奇数集上的中间数，或2)偶数集上两个中间数的平均值。

所以，这里是魔术贴的解决方案修补处理奇数和偶数集:

SELECT AVG(middle_values) AS 'median' FROM (
  SELECT t1.median_column AS 'middle_values' FROM
    (
      SELECT @row:=@row+1 as `row`, x.median_column
      FROM median_table AS x, (SELECT @row:=0) AS r
      WHERE 1
      -- put some where clause here
      ORDER BY x.median_column
    ) AS t1,
    (
      SELECT COUNT(*) as 'count'
      FROM median_table x
      WHERE 1
      -- put same where clause here
    ) AS t2
    -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
    WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;

要使用它，请遵循以下3个简单步骤:

将上面代码中的“median_table”(出现2次)替换为您的表名 将“median_column”(3次)替换为您希望为其查找中位数的列名 如果你有一个WHERE条件，用WHERE条件替换“WHERE 1”(2次)

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

对于几乎所有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

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

让我们创建一个名为numbers的示例表

这个答案是针对mysql数据库的

在postgres Sql中，它简单地使用per_cont函数

创建表数字( num INT, 频率整数 ）;

在数字表中插入值

插入数字 (7) 0 (1, 1), (2、3), (1) 3 (9,1), (1, 1), (2、3), (1) 3 (9,1);

——select * from numbers

作为递归num_frequency (num,frequency, i) （ 选择num,频率,1 从数字 UNION ALL 选择num,频率,i + 1 从num_frequency num_frequency的地方。I < num_frequency.frequency ）

select * (max(当numbers=lower_limit时，则num else null end)/2 +max(当数字=upper_limit时，则num else null end)/2)作为中位数 从( select *, total_number % 2, 情况下 当total_number%2=0时，total_number/2 Else (total_number+1)/2 end as lower_limit， 情况下 当total_number%2=0时，total_number/2+1 其他(total_number + 1) / 2 结束为upper_limit

从( Select *，max(numbers) over() as total_number from ( Select num,row_number() over(按num排序) 作为num_frequency中的数字 b) b) b)

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

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>

按维度分组的中位数:

SELECT your_dimension, avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 AS `row_number`,
   IF(@dim <> d.your_dimension, @rownum := 0, NULL),
   @dim := d.your_dimension AS your_dimension,
   d.val
   FROM data d,  (SELECT @rownum:=0) r, (SELECT @dim := 'something_unreal') d
  WHERE 1
  -- put some where clause here
  ORDER BY d.your_dimension, d.val
) as t1
INNER JOIN  
(
  SELECT d.your_dimension,
    count(*) as total_rows
  FROM data d
  WHERE 1
  -- put same where clause here
  GROUP BY d.your_dimension
) as t2 USING(your_dimension)
WHERE 1
AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) )

GROUP BY your_dimension;