知道确切的行数，你可以使用这个查询:

SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>

Where <half> = ceiling(<size> / 2.0) - 1

对于一个表站和列lat_n，下面是MySQL代码来获得中位数:

set @rows := (select count(1) from station);
set @v1 := 0;
set @sql1 := concat('select lat_n into @v1 from station order by lat_n asc limit 1 offset ', ceil(@rows/2) - 1);
prepare statement1 from @sql1;
execute statement1;
set @v2 := 0;
set @sql2 := concat('select lat_n into @v2 from station order by lat_n asc limit 1 offset ', ceil((@rows + 1)/2) - 1);
prepare statement2 from @sql2;
execute statement2;
select (@v1 + @v2)/2;

如果这是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

你也可以选择在存储过程中这样做:

DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN
  -- Set default parameters
  IF where_clause IS NULL OR where_clause = '' THEN
    SET where_clause = 1;
  END IF;

  -- Prepare statement
  SET @sql = CONCAT(
    "SELECT AVG(middle_values) AS 'median' FROM (
      SELECT t1.", column_name, " AS 'middle_values' FROM
        (
          SELECT @row:=@row+1 as `row`, x.", column_name, "
          FROM ", table_name," AS x, (SELECT @row:=0) AS r
          WHERE ", where_clause, " ORDER BY x.", column_name, "
        ) AS t1,
        (
          SELECT COUNT(*) as 'count'
          FROM ", table_name, " x
          WHERE ", where_clause, "
        ) 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
    ");

  -- Execute statement
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END//
DELIMITER ;


-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);

我有一个包含大约10亿行的数据库，我们需要它来确定集合中的年龄中位数。对十亿行进行排序是困难的，但如果你将可以找到的不同值(年龄范围从0到100)聚合在一起，你可以对这个列表进行排序，并使用一些算术魔术来找到你想要的任何百分位数，如下所示:

with rawData(count_value) as
(
    select p.YEAR_OF_BIRTH
        from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
  select avg(1.0 * count_value) as avg_value,
    stdev(count_value) as stdev_value,
    min(count_value) as min_value,
    max(count_value) as max_value,
    count(*) as total
  from rawData
),
aggData (count_value, total, accumulated) as
(
  select count_value, 
    count(*) as total, 
        SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated
  FROM rawData
  group by count_value
)
select o.total as count_value,
  o.min_value,
    o.max_value,
    o.avg_value,
    o.stdev_value,
    MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
    MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
    MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
    MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
    MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value
from aggData d
cross apply overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
;

这个查询取决于你的db支持窗口函数(包括ROWS UNBOUNDED precede)，但如果你没有，这是一个简单的事情，将aggData CTE与自身连接，并将所有先前的总数聚合到' cumulative '列，用于确定哪个值包含指定的预分词。上面的示例计算p10、p25、p50(中位数)、p75和p90。

屁股的

我使用了两个查询方法:

第一个得到count, min, Max和avg 第二个语句(预处理语句)使用“LIMIT @count/ 2,1”和“ORDER BY ..”子句来获得中值

它们被包装在函数defn中，因此可以从一次调用中返回所有值。

如果您的范围是静态的，并且数据不经常更改，那么预先计算/存储这些值并使用存储的值，而不是每次都从头查询，可能会更有效。