分数 时间
6666 2012-08-14 11:26:59
5555 2012-08-10 19:18:12
600 2012-08-14 17:55:13
600 2012-08-14 17:29:14
600 2012-08-14 17:15:21
600 2012-08-14 16:49:00結果6666 2012-08-14 11:26:59 1
5555 2012-08-10 19:18:12 2
600 2012-08-14 17:55:13 3
600 2012-08-14 17:29:14 4
600 2012-08-14 17:15:21 5
600 2012-08-14 16:49:00 6
這樣的排名查詢怎麼寫法?
6666 2012-08-14 11:26:59
5555 2012-08-10 19:18:12
600 2012-08-14 17:55:13
600 2012-08-14 17:29:14
600 2012-08-14 17:15:21
600 2012-08-14 16:49:00結果6666 2012-08-14 11:26:59 1
5555 2012-08-10 19:18:12 2
600 2012-08-14 17:55:13 3
600 2012-08-14 17:29:14 4
600 2012-08-14 17:15:21 5
600 2012-08-14 16:49:00 6
這樣的排名查詢怎麼寫法?
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([分数] INT,[时间] DATETIME)
INSERT [tb]
SELECT 6666,'2012-08-14 11:26:59' UNION ALL
SELECT 5555,'2012-08-10 19:18:12' UNION ALL
SELECT 600,'2012-08-14 17:55:13' UNION ALL
SELECT 600,'2012-08-14 17:29:14' UNION ALL
SELECT 600,'2012-08-14 17:15:21' UNION ALL
SELECT 600,'2012-08-14 16:49:00'
--------------开始查询--------------------------SELECT *,shortid=(select COUNT(*)+1 FROM tb WHERE [分数]>t.[分数] or ([分数]=t.[分数] AND [时间]>t.[时间]))
FROM [tb] t
----------------结果----------------------------
/*
分数 时间 shortid
----------- ----------------------- -----------
6666 2012-08-14 11:26:59.000 1
5555 2012-08-10 19:18:12.000 2
600 2012-08-14 17:55:13.000 3
600 2012-08-14 17:29:14.000 4
600 2012-08-14 17:15:21.000 5
600 2012-08-14 16:49:00.000 6(6 行受影响)
*/