mysql 表数据排序_SQL数据排序_MySQL

--测试表

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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网

TAG标签：10测试表