使用一条语句——一种方法是使用ROW_NUMBER()， COUNT()窗口函数并过滤子查询。下面是薪资中位数:

 SELECT AVG(e_salary) 
 FROM                                                             
    (SELECT 
      ROW_NUMBER() OVER(ORDER BY e_salary) as row_no, 
      e_salary,
      (COUNT(*) OVER()+1)*0.5 AS row_half
     FROM Employee) t
 WHERE row_no IN (FLOOR(row_half),CEILING(row_half))

我在网上看到过类似的解决方案，使用地板和天花板，但尝试使用单一的语句。(编辑)

使用一条语句——一种方法是使用ROW_NUMBER()， COUNT()窗口函数并过滤子查询。下面是薪资中位数:

 SELECT AVG(e_salary) 
 FROM                                                             
    (SELECT 
      ROW_NUMBER() OVER(ORDER BY e_salary) as row_no, 
      e_salary,
      (COUNT(*) OVER()+1)*0.5 AS row_half
     FROM Employee) t
 WHERE row_no IN (FLOOR(row_half),CEILING(row_half))

我在网上看到过类似的解决方案，使用地板和天花板，但尝试使用单一的语句。(编辑)

对于连续变量/测量'table1'中的'col1'

select col1  
from
    (select top 50 percent col1, 
    ROW_NUMBER() OVER(ORDER BY col1 ASC) AS Rowa,
    ROW_NUMBER() OVER(ORDER BY col1 DESC) AS Rowd
    from table1 ) tmp
where tmp.Rowa = tmp.Rowd

以下是我的解决方案:

with tempa as

 (

    select value,row_number() over (order by value) as Rn,/* Assigning a 
                                                           row_number */
           count(value) over () as Cnt /*Taking total count of the values */
    from numbers
    where value is not null /* Excluding the null values */
 ),

tempb as

  (

    /* Since we don't know whether the number of rows is odd or even, we shall 
     consider both the scenarios */

    select round(cnt/2) as Ref from tempa where mod(cnt,2)=1
    union all
    select round(cnt/2) a Ref from tempa where mod(cnt,2)=0
     union all
    select round(cnt/2) + 1 as Ref from tempa where mod(cnt,2)=0
   )
  select avg(value) as Median_Value

  from tempa where rn in

    ( select Ref from tempb);

从员工表中得到工资的中位数

with cte as (select salary, ROW_NUMBER() over (order by salary asc) as num from employees)

select avg(salary) from cte where num in ((select (count(*)+1)/2 from employees), (select (count(*)+2)/2 from employees));

试试下面的逻辑来找出中位数:

考虑一个包含以下数字的表格: 1、1、2、3、4、5所示

中位数是2.5

with tempa as 
(
    select num,count(num) over() as Cnt,
        row_number() over (order by num) as Rnum
    from temp),
tempb as
    (
        select round(cnt/2) as ref_value
        from tempa where mod(cnt,2)<>0
        union all
        select round(cnt/2) from tempa where mod(cnt,2)=0
        union all
        select round(cnt/2+1)
        from tempa where mod(cnt,2)=0
    )
select avg(num) from tempa
where rnum in (select * from tempb);