22,210
社区成员
发帖
与我相关
我的任务
分享
select xh.snum 学号,xh.sname 姓名,
sum(case kcb.kcmc when '语文' then fs else 0 end)as 语文,
sum(case kcb.kcmc when '数学' then fs else 0 end)as 数学,
sum(case kcb.kcmc when '英语' then fs else 0 end)as 英语,
sum(case kcb.kcmc when '物理' then fs else 0 end)as 物理,
sum(case kcb.kcmc when '化学' then fs else 0 end)as 化学,
sum(case kcb.kcmc when '政治' then fs else 0 end)as 政治,
sum(cast(fs as numeric(5,1))) as 总分 /*将数据类型转换为numeric,长度5,一位小数,因varchar类型不能直接运算,所以需先转换类型; */
from xh
inner join cj on xh.snum=cj.snum
inner join kcb on cj.kcdm=kcb.kcdm
group by xh.snum,xh.sname
;WITH t AS (
select
ROW_NUMBER() OVER (ORDER BY sum(cast(fs as numeric(5,1))) DESC) AS rid
,xh.snum 学号
,xh.sname 姓名,
sum(case kcb.kcmc when '语文' then fs else 0 end)as 语文,
sum(case kcb.kcmc when '数学' then fs else 0 end)as 数学,
sum(case kcb.kcmc when '英语' then fs else 0 end)as 英语,
sum(case kcb.kcmc when '物理' then fs else 0 end)as 物理,
sum(case kcb.kcmc when '化学' then fs else 0 end)as 化学,
sum(case kcb.kcmc when '政治' then fs else 0 end)as 政治,
sum(cast(fs as numeric(5,1))) as 总分 /*将数据类型转换为numeric,长度5,一位小数,因varchar类型不能直接运算,所以需先转换类型; */
from xh
inner join cj on xh.snum=cj.snum
inner join kcb on cj.kcdm=kcb.kcdm
group by xh.snum,xh.sname
)
SELECT * FROM t ORDER BY rid
http://www.cnblogs.com/xhyang110/archive/2009/10/27/1590448.html
-- 参考这个
select max(object_id) , type ,
row_number() over(order by max(object_id) desc) rn
from sys.objects group by type