34,838
社区成员




update 表
set 学号='2008'+right(学号,2)
where 标志='F'
update 表 set 学号='2008'+right(学号,2) where 标志='F'
--search
select m.姓名,m.年级,m.班级,m.标志, 学号='2008'+ right('00'+cast(m.px as varchar),2) from
(
select t.* , px = (select count(1) from tb where (年级=t.年级 and 班级 < t.班级) or (年级=t.年级 and 班级 = t.班级 and 姓名 < t.姓名) ) + 1 from tb t
) t
--update
update tb
set 学号 = tb2.学号
from tb tb1,(select m.姓名,m.年级,m.班级,m.标志, 学号='2008'+ right('00'+cast(m.px as varchar),2) from
(
select t.* , px = (select count(1) from tb where (年级=t.年级 and 班级 < t.班级) or (年级=t.年级 and 班级 = t.班级 and 姓名 < t.姓名) ) + 1 from tb t
) t
) tb2
where tb1.年级=tb2.年级 and tb1.班级 = tb2.班级 and tb1.姓名 = tb2.姓名
表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 50
1. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
2. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8