两种形式的分组排名

--建表
CREATE TABLE TEST
(
[ID] INT,
IDS DECIMAL(18,4),
[TIME] DATETIME
)

--测试数据
INSERT INTO TEST
SELECT 20.36,1,'2009-09-30'
UNION ALL
SELECT 20.36,1,'2009-09-30'
UNION ALL
SELECT 14.37,1,'2009-09-30'
UNION ALL
SELECT 23.56,1,'2009-09-30'
UNION ALL
SELECT 25.16,1,'2009-09-30'
UNION ALL
SELECT 20.36,2,'2009-06-30'
UNION ALL
SELECT 14.37,2,'2009-06-30'
UNION ALL
SELECT 23.56,2,'2009-06-30'
UNION ALL
SELECT 25.16,2,'2009-06-30'
UNION ALL
SELECT NULL,2,'2009-06-30'

--形式1：
--此形式排名结果类似于1 2 2 3 的结果显示
SELECT *,ORID=(SELECT COUNT(DISTINCT IDS)--IDS 排序字段
               FROM TEST
               WHERE IDS>=A.IDS AND [ID]=A.[ID] AND [TIME]=A.[TIME]
               GROUP BY [TIME],[ID] --分类字段
              )
FROM TEST AS A

--形式2：
--此形式排名结果类似于1 2 2 3 的结果显示
SELECT *,ORID=CASE
                  WHEN IDS IS NULL THEN NULL
                  ELSE ISNULL((SELECT COUNT(IDS)--IDS 排序字段
                                 FROM TEST
                               WHERE IDS>A.IDS AND [ID]=A.[ID] AND [TIME]=A.[TIME]
                               GROUP BY [TIME],[ID] --分类字段
                             ),0)+1 END
FROM TEST AS A