34,588
社区成员
发帖
与我相关
我的任务
分享
declare @score table (
sid int,
cid int,
score int
)
insert @score select
1, 1, 88
union all select
2, 1, 92
union all select
3, 1, 88
union all select
1, 2, 98
union all select
2, 2, 98
union all select
3, 2, 78
union all select
4, 2, 90
select c.cid,avg(r.score) as score
from (
select * ,(select count(1) from @score where cid=a.cid and (score<a.score or score=a.score and sid<=a.sid)) as Num
from @score a
) as r,(select cid,count(1) as cnt from @score group by cid) as c
where r.cid=c.cid
and (c.cnt % 2 = 0
and (r.num = c.cnt / 2
or r.num = c.cnt / 2 +1)
or c.cnt % 2 = 1
and r.num = (c.cnt+1) / 2
)
group by c.cid
--结果
cid score
----------- -----------
1 88
2 94
(2 行受影响)