CREATE TABLE test(ID INT NOT NULL)
GO
INSERT test SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 11
UNION ALL SELECT 23
GO
SELECT * FROM test
/*
1
2
3
4
5
6
11
23
*/
GO
UPDATE a SET ID=CNT
FROM test a
INNER JOIN (SELECT ID,(SELECT COUNT(*) FROM test WHERE id<=a.id) cnt FROM test a) b
ON a.id=b.id
GO
SELECT * FROM test
/*
1
2
3
4
5
6
7
8
*/
GO
DROP TABLE test
GO
GO
INSERT test SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 11
UNION ALL SELECT 23
GO
SELECT * FROM test
/*
1
2
3
4
5
6
11
23
*/
GO
UPDATE a SET ID=CNT
FROM test a
INNER JOIN (SELECT ID,(SELECT COUNT(*) FROM test WHERE id<=a.id) cnt FROM test a) b
ON a.id=b.id
GO
SELECT * FROM test
/*
1
2
3
4
5
6
7
8
*/
GO
DROP TABLE test
GO
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式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 62. 名次生成方式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
INSERT INTO test values(1)
INSERT INTO test values(2)
INSERT INTO test values(3)
INSERT INTO test values(4)
INSERT INTO test values(5)
INSERT INTO test values(6)
INSERT INTO test values(11)
INSERT INTO test values(23)
GOupdate test
set id = m.px
from test,(SELECT id , px = (select count(id) from test where id < t.id)+1 from test t) m
where test.id = m.idselect * from testdrop table test/*
ID
-----------
1
2
3
4
5
6
7
8(所影响的行数为 8 行)
*/
set id = m.px
from test,(SELECT id , px = (select count(id) from test where id < t.id)+1 from test t) m
where test.id = m.id and m.px!=m.id
/*(所影响的行数为 2 行)*/