有表:SSJC 结构:
id roomid score xb
1 101 85 jsj
2 102 85 jsj
3 205 90 hg
4 206 70 hg由上表统计结果插入下表:
统计表:SSCJ_Conts结构 id xb avg num
1 jsj 85 1
2 hg 80 2 注:( num 名次)问 : 如何用存储过程实现以上统计功能
id roomid score xb
1 101 85 jsj
2 102 85 jsj
3 205 90 hg
4 206 70 hg由上表统计结果插入下表:
统计表:SSCJ_Conts结构 id xb avg num
1 jsj 85 1
2 hg 80 2 注:( num 名次)问 : 如何用存储过程实现以上统计功能
insert into @t
select 1, 101, 85, 'jsj'
union all select 2, 102, 85, 'jsj'
union all select 3, 205, 90, 'hg'
union all select 4, 206, 70, 'hg'select xb, avg(score) as avg,num=identity(int,1,1) into #tt from @t group by xb order by avg(score) descselect * from #tt
/*
xb avg num
--------------------------------------------------
jsj 85 2
hg 80 1
*/
insert into @t
select 1, 101, 85, 'jsj'
union all select 2, 102, 85, 'jsj'
union all select 3, 205, 90, 'hg'
union all select 4, 206, 70, 'hg' select xb, avg(score) as avg into #tt from @t group by xb
select *,(select count(*) from #tt where avg>=a.avg) as num from #tt a order by avg desc
/*
xb avg num
--------------------------------------------------
jsj 85 1
hg 80 2
*/
select *,(select count(*) from #tt where avg>=a.avg) as num from #tt a order by avg desc