SQL數(shù)據(jù)排序_MySQL

--插入數(shù)據(jù)
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

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com