jxpf(评分表)szbianhao pinggu
SZ2006050069 12
SZ2006050069 50
sz0001 30
sz0001 40
shizi(教师表)
bianhao
SZ2006050069
sz0001
1002
要求:按教师求总评分并排名
结果:bianhao pinggu pm
sz0001 70 1
SZ2006050069 62 2
1002 NULL(未评分) 3
十万火急 谢谢了 !!
SZ2006050069 12
SZ2006050069 50
sz0001 30
sz0001 40
shizi(教师表)
bianhao
SZ2006050069
sz0001
1002
要求:按教师求总评分并排名
结果:bianhao pinggu pm
sz0001 70 1
SZ2006050069 62 2
1002 NULL(未评分) 3
十万火急 谢谢了 !!
insert into @t1 select 'SZ2006050069',12
insert into @t1 select 'SZ2006050069',50
insert into @t1 select 'sz0001' ,30
insert into @t1 select 'sz0001' ,40
declare @t2 table(bianhao varchar(12))
insert into @t2 select 'SZ2006050069'
insert into @t2 select 'sz0001'
insert into @t2 select '1002'
select
c.bianhao,c.pinggu,isnull(count(d.bianhao),0) as num
from
(select a.bianhao,sum(b.pinggu) pinggu from @t2 a left join @t1 b on a.bianhao=b.szbianhao group by a.bianhao) c,
(select a.bianhao,sum(b.pinggu) pinggu from @t2 a left join @t1 b on a.bianhao=b.szbianhao group by a.bianhao) d
where
isnull(c.pinggu,0)<=isnull(d.pinggu,0)
group by
c.bianhao,c.pinggu
order by
num/*
bianhao pinggu num
------------ ----------- -----------
sz0001 70 1
SZ2006050069 62 2
1002 NULL 3
*/
select szbianhao,sum(pinggu) as sumpinggu,IDENTITY(int,1,1) as pm into #tt from jxpf group by szbianhao
select * from shizi left join #tt on shizi.bianhao=#tt.szbianhao
create table jxpf
(
szbianhao varchar(20),
pinggu int
)create table shizi
(
bianhao varchar(20)
)insert into jxpf select 'SZ2006050069', 12
insert into jxpf select 'SZ2006050069', 50
insert into jxpf select 'sz0001', 30
insert into jxpf select 'sz0001', 40insert into shizi select 'SZ2006050069'
insert into shizi select 'sz0001'
insert into shizi select '1002'
select bianhao,pinggu,pm = (select count(1)+1 from (select szbianhao,sum(pinggu) as pinggu from jxpf group by szbianhao)t4 where pinggu > t3.pinggu2 )
from
(select t1.bianhao,isnull(cast(t2.pinggu as varchar),'未评分') as pinggu,isnull(cast(t2.pinggu as varchar),0) as pinggu2
from shizi t1 left join (select szbianhao,sum(pinggu) as pinggu from jxpf group by szbianhao) t2
on t1.bianhao = t2.szbianhao
)t3
order by pm
SZ2006050069 62 2
1002 未评分 3