/*中位数定义：将数从小到大排列，若总数为奇数，取中间位置的数值。若总数为偶数，取中间位置两个数的平均值。*/
create table #tb(num int)
declare @i int
set @i=1
while @i<10    --#tb的行数可为奇数，也可为偶数
begin
     insert into #tb values(cast(RAND()*100 as int))   --表中可能有重复值
     set @i=@i+1
end

create table #tb(num int)
declare @i int
set @i=1
while @i<10    --#tb的行数可为奇数，也可为偶数
begin
     insert into #tb values(cast(RAND()*100 as int))   --表中可能有重复值
     set @i=@i+1
end

select * from #tb order by num asc
if OBJECT_ID('pro_test')is not null
drop proc pro_test
go
create proc pro_test
as
declare @count int
select @count=COUNT(1) from #tb
if @count%2=0
begin
   select AVG(num) as 中位数 from(
   select 
       ROW_NUMBER()over(order by num) as id,num 
   from 
       #tb
   )t where id in(@count/2,(@count/2)+1)
end
else
begin
   select num as 中位数 from(
   select 
       ROW_NUMBER()over(order by num) as id,num 
   from 
       #tb
   )t where id=(@count/2)+1
end


exec pro_test

/*
中位数
42
*/

------解决方案--------------------

SQL code

/*中位数定义：将数从小到大排列，若总数为奇数，取中间位置的数值。若总数为偶数，取中间位置两个数的平均值。*/
--奇数测试
if OBJECT_ID('tb','U') is not null drop table tb
go
create table tb(num int)
--得到数据
declare @i int
set @i=1
while @i<10    --#tb的行数可为奇数，也可为偶数
begin
     insert into tb values(cast(RAND()*100 as int))   --表中可能有重复值
     set @i=@i+1
end

--奇数结果  若总数为奇数，取中间位置的数值 位置 为5的
if (select COUNT(1)%2 from tb)=1
Begin
    with cte as
    (
        select 
            num,
            ROW_NUMBER() over(order by num) as v_squence
        from tb 
    )select a.num from cte a
    where a.v_squence =(select COUNT(1)/2+1 from cte )
End
else  --偶数结果  取中间位置两个数的平均值。
Begin
    with cte as
    (
        select 
            num,
            ROW_NUMBER() over(order by num) as v_squence
        from tb 
    )select 
        sum(num)/2 
    from cte a
    where a.v_squence =(select COUNT(1)/2+1 from cte ) or
          a.v_squence =(select COUNT(1)/2 from cte )
end