SQL怎么求统计学中的中位数？

[liukeya2008]

/*中位数定义：将数从小到大排列，若总数为奇数，取中间位置的数值。若总数为偶数，取中间位置两个数的平均值。*/
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
*/

[bean_sql]

/*中位数定义：将数从小到大排列，若总数为奇数，取中间位置的数值。若总数为偶数，取中间位置两个数的平均值。*/
--奇数测试
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

[风一样的大叔]

http://blog.csdn.net/qiujialongjjj/article/details/8007000