34,838
社区成员




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 行受影响)