select header,line,0 ident into #a from 表 order by header
declare @a int,@b int
update #a set @b=case when @a=header then @b+1 else 1 end,@a=header,ident=@bselect * from #a
declare @a int,@b int
update #a set @b=case when @a=header then @b+1 else 1 end,@a=header,ident=@bselect * from #a
select *,(select sum(1) from 表 where header=tem.header and line<=tem.line) ident from 表 tem
INSERT INTO #score
SELECT '張三',100
UNION
SELECT '李四',89
UNION
SELECT '王五',64
UNION
SELECT '趙六',95
UNION
SELECT '劉七',67
UNION
SELECT '楊八',89
UNION
SELECT '朱九',95
SELECT (
SELECT COUNT(*)
FROM
(SELECT DISTINCT score
FROM #score
)B
WHERE B.score>=A.score ) AS '名次',*
FROM #score A--按名次排序1
SELECT (
SELECT COUNT(*)
FROM
(SELECT DISTINCT score
FROM #score
)B
WHERE B.score>=A.score ) AS '名次',*
FROM #score A
ORDER BY (
SELECT COUNT(*)
FROM
(SELECT DISTINCT score
FROM #score
)B
WHERE B.score>=A.score )