@sql=
,[数学]=sum(case km when '数学' then cj else 0 end),
[语文]=sum(case km when '语文' then cj else 0 end)@sql1=
,[数学名次]=(select sum(1) from # where [数学]>=a.[数学]),
[语文名次]=(select sum(1) from # where [语文]>=a.[语文])select
xh 学号,
xm 姓名,
[数学]=sum(case km when '数学' then cj else 0 end),
[语文]=sum(case km when '语文' then cj else 0 end),
总成绩=sum(cj),
总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select *,[数学名次]=(select sum(1) from # where [数学]>=a.[数学]),[语文名次]=(select sum(1) from # where [语文]>=a.[语文]) from # a
,[数学]=sum(case km when '数学' then cj else 0 end),
[语文]=sum(case km when '语文' then cj else 0 end)@sql1=
,[数学名次]=(select sum(1) from # where [数学]>=a.[数学]),
[语文名次]=(select sum(1) from # where [语文]>=a.[语文])select
xh 学号,
xm 姓名,
[数学]=sum(case km when '数学' then cj else 0 end),
[语文]=sum(case km when '语文' then cj else 0 end),
总成绩=sum(cj),
总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select *,[数学名次]=(select sum(1) from # where [数学]>=a.[数学]),[语文名次]=(select sum(1) from # where [语文]>=a.[语文]) from # a
我得理解是;这个sql语句的总体结构分成两个部分,分两次执行
1.
select xh,xm,各门功课的成绩,总成绩,总名次
into 一个临时表 from #t
然后再这个临时表的基础上来总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa) ,这条语句aa我得理解是一个临时表的别名,而这里为什么
sum(a.cj)<=aa能够行的通?sum(a.cj)应该与该表的字段比较,为什么不是这样写sum(a.cj)<=aa.aa
这是我改写的一个例子。KSCJ是一个基表。select XH 学号, XM 姓名,
[数学]=sum(case KCMC when '数学' then KSCJ else 0 end),
[物理]=sum(case KCMC when '物理' then KSCJ else 0 end),
[英语]=sum(case KCMC when '英语' then KSCJ else 0 end),
[语文]=sum(case KCMC when '语文' then KSCJ else 0 end),
总成绩=sum(KSCJ),
总名次=(select sum(1) from(select XH,zcj=sum(KSCJ) from KSCJ ) b where sum(c.KSCJ)<=b.zcj)
into #tb
from KSCJ group by xh,xm select * ,
[数学名次]=(select sum(1) from #tb where [数学]>=c.[数学]),
[物理名次]=(select sum(1) from #tb where [物理]>=c.[物理]),
[英语名次]=(select sum(1) from #tb where [英语]>=c.[英语]),
[语文名次]=(select sum(1) from #tb where [语文]>=c.[语文])
from #tb c错误消息:
服务器: 消息 107,级别 16,状态 2,行 31
列前缀 'c' 与查询中所用的表名或别名不匹配。
[数学]=sum(case KCMC when '数学' then KSCJ else 0 end),
[物理]=sum(case KCMC when '物理' then KSCJ else 0 end),
[英语]=sum(case KCMC when '英语' then KSCJ else 0 end),
[语文]=sum(case KCMC when '语文' then KSCJ else 0 end),
总成绩=sum(KSCJ),
总名次=(select sum(1) from(select XH,zcj=sum(KSCJ) from KSCJ ) b where sum(a.KSCJ)<=b.zcj) --这里的sum(a.KSCJ)中的别名a与下面添加的匹配
into #tb
from KSCJ a group by xh,xm --怎么能丢掉一个别名呢?select *,
[数学名次]=(select sum(1) from #tb where [数学]>=c.[数学]),
[物理名次]=(select sum(1) from #tb where [物理]>=c.[物理]),
[英语名次]=(select sum(1) from #tb where [英语]>=c.[英语]),
[语文名次]=(select sum(1) from #tb where [语文]>=c.[语文])
from #tb c