我最初的回答是:

select  max(my_column) as [my_column], quartile
from    (select my_column, ntile(4) over (order by my_column) as [quartile]
         from   my_table) i
--where quartile = 2
group by quartile

这将使您一举获得中位数和四分位范围。如果你真的只想要一行作为中值，那么取消注释where子句。

当你把它放入解释计划时，60%的工作是对数据进行排序，这在计算像这样的位置依赖统计数据时是不可避免的。

我修改了答案，以遵循Robert Ševčík-Robajz在下面的评论中提出的优秀建议:

;with PartitionedData as
  (select my_column, ntile(10) over (order by my_column) as [percentile]
   from   my_table),
MinimaAndMaxima as
  (select  min(my_column) as [low], max(my_column) as [high], percentile
   from    PartitionedData
   group by percentile)
select
  case
    when b.percentile = 10 then cast(b.high as decimal(18,2))
    else cast((a.low + b.high)  as decimal(18,2)) / 2
  end as [value], --b.high, a.low,
  b.percentile
from    MinimaAndMaxima a
  join  MinimaAndMaxima b on (a.percentile -1 = b.percentile) or (a.percentile = 10 and b.percentile = 10)
--where b.percentile = 5

当您有偶数个数据项时，这应该计算正确的中位数和百分比值。同样，如果您只想要中位数而不是整个百分位数分布，请取消最后的where子句的注释。

简单、快速、准确

SELECT x.Amount 
FROM   (SELECT amount, 
               Count(1) OVER (partition BY 'A')        AS TotalRows, 
               Row_number() OVER (ORDER BY Amount ASC) AS AmountOrder 
        FROM   facttransaction ft) x 
WHERE  x.AmountOrder = Round(x.TotalRows / 2.0, 0)  

查看SQL中位数计算的其他解决方案: “用MySQL计算中位数的简单方法”(解决方案大多与供应商无关)。

在Jeff Atwood的答案的基础上，它是用GROUP BY和一个相关的子查询来获得每个组的中位数。

SELECT TestID, 
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score DESC) AS TopHalf)
) / 2 AS MedianScore,
AVG(Score) AS AvgScore, MIN(Score) AS MinScore, MAX(Score) AS MaxScore
FROM Posts_parent
GROUP BY Posts_parent.TestID

我最初的回答是:

select  max(my_column) as [my_column], quartile
from    (select my_column, ntile(4) over (order by my_column) as [quartile]
         from   my_table) i
--where quartile = 2
group by quartile

这将使您一举获得中位数和四分位范围。如果你真的只想要一行作为中值，那么取消注释where子句。

当你把它放入解释计划时，60%的工作是对数据进行排序，这在计算像这样的位置依赖统计数据时是不可避免的。

我修改了答案，以遵循Robert Ševčík-Robajz在下面的评论中提出的优秀建议:

;with PartitionedData as
  (select my_column, ntile(10) over (order by my_column) as [percentile]
   from   my_table),
MinimaAndMaxima as
  (select  min(my_column) as [low], max(my_column) as [high], percentile
   from    PartitionedData
   group by percentile)
select
  case
    when b.percentile = 10 then cast(b.high as decimal(18,2))
    else cast((a.low + b.high)  as decimal(18,2)) / 2
  end as [value], --b.high, a.low,
  b.percentile
from    MinimaAndMaxima a
  join  MinimaAndMaxima b on (a.percentile -1 = b.percentile) or (a.percentile = 10 and b.percentile = 10)
--where b.percentile = 5

当您有偶数个数据项时，这应该计算正确的中位数和百分比值。同样，如果您只想要中位数而不是整个百分位数分布，请取消最后的where子句的注释。

with t1 as (select *, row_number() over(order by ordqty) as rn,
count(*) over() as rc from ord_line)
select rn,* from t1 where rn in((rc+1)/2, (rc+2)/2);

它将计算偶数和奇数的中位数。

Ord_line是一个表 Ordqty是一个列