SQL数据排序

--测试表
CREATE TABLE Test(F1 char(10), F2 char(10))

--插入数据
INSERT INTO Test
SELECT @#a@# F1, @#1@# F2
UNION
SELECT @#b@# F1, @#2@# F2
UNION
SELECT @#c@# F1, @#4@# F2
UNION
SELECT @#d@# F1, @#3@# F2
UNION
SELECT @#e@# F1, @#4@# F2
UNION
SELECT @#f@# F1, @#5@# F2
UNION
SELECT @#g@# F1, @#4@# F2
UNION
SELECT @#h@# F1, @#7@# F2
UNION
SELECT @#i@# F1, @#9@# F2

---排名次
--方法1
SELECT a.*,(SELECT COUNT(*) FROM test b WHERE b.F2>a.F2)+1 AS minci FROM test a ORDER BY minci
--方法2
SELECT id = IDENTITY (int, 0, 1), f1, f2 INTO #t FROM test ORDER BY F2 DESC
SELECT a.f1, a.f2, a.id + 1 - cast(id - cc - minn AS Char(10)) AS [名次]
FROM #t a, (SELECT f2, cc, minn FROM (SELECT f2, COUNT(*) AS cc, MIN(id) - COUNT(*) AS minn FROM #t GROUP BY f2) t) b
WHERE a.f2 = b.f2
ORDER BY a.f2 DESC

--删除表
DROP TABLE #t
DROP TABLE test

文章来源于领测软件测试网 https://www.ltesting.net/