排名问题？急～～～～

[简单dian]

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


十万火急 谢谢了 ！！

[简单dian]

自己顶一下吧

[子陌红尘]

declare @t1 table(szbianhao varchar(12),pinggu int)
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
*/

[csii2000]

select szbianhao,sum(pinggu),IDENTITY(int,1,1) as pm from jxpf group by szbianhao

[简单dian]

十分感谢

[liangpei2008]

子陌厉害！学习

[csii2000]

上面发错了，修改如下
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', 40

insert 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

[冷箫轻笛]

结果

sz0001 70 1
SZ2006050069 62 2
1002 未评分 3