QL查询案例：取得分组TOP-N

【转】SQL查询案例：取得分组 TOP-N CREATE TABLE TopnTest ( name VARCHAR(10), --姓名 procDate DATETIME, --处理时间 result INT --成绩 ); INSERT INTO TopnTest VALUES('张三', '2010-10-01 12:00:05', 80); INSERT INTO TopnTest VALUES('张三', '2010

CREATE TABLE TopnTest (

name VARCHAR(10), --姓名

procDate DATETIME, --处理时间

result INT --成绩

);

INSERT INTO TopnTest VALUES('张三', '2010-10-01 12:00:05', 80);

INSERT INTO TopnTest VALUES('张三', '2010-10-01 12:20:05', 85);

INSERT INTO TopnTest VALUES('张三', '2010-10-02 07:25:15', 79);

INSERT INTO TopnTest VALUES('张三', '2010-10-02 10:30:05', 88);

INSERT INTO TopnTest VALUES('张三', '2010-10-03 15:05:05', 86);

INSERT INTO TopnTest VALUES('李四', '2010-10-01 06:00:05', 60);

INSERT INTO TopnTest VALUES('李四', '2010-10-04 08:00:05', 90);

INSERT INTO TopnTest VALUES('李四', '2010-10-05 10:00:05', 75);

INSERT INTO TopnTest VALUES('李四', '2010-10-08 11:00:05', 88);

INSERT INTO TopnTest VALUES('李四', '2010-10-09 12:00:05', 60);

INSERT INTO TopnTest VALUES('王五', '2010-09-10 08:00:05', 70);

INSERT INTO TopnTest VALUES('王五', '2010-09-14 08:00:05', 80);

INSERT INTO TopnTest VALUES('王五', '2010-09-25 18:00:05', 75);

INSERT INTO TopnTest VALUES('王五', '2010-09-28 18:00:05', 88);

INSERT INTO TopnTest VALUES('王五', '2010-10-09 12:00:05', 70);

要求

取得每个人的最近2次处理时间的详细记录情况。

思路

如果仅仅是一个人的最近2次，那么直接TOP 2或者Rownum <= 2可以处理。

每个人的最近1次，也可以通过SELECT MAX() GROUP BY来实现。

每个人的最近2次，需要自己和自己关联，才能解决了。

实现

SELECT

*

FROM

TopnTest

WHERE

( SELECT

COUNT(1)

FROM

TopnTest subTopnTest

WHERE

TopnTest.name = subTopnTest.name

AND TopnTest.procDate < subTopnTest.procDate

) < 2

ORDER BY

name, procDate

执行结果

name procDate result

---------- ----------------------- -----------

李四 2010-10-08 11:00:05.000 88

李四 2010-10-09 12:00:05.000 60

王五 2010-09-28 18:00:05.000 88

王五 2010-10-09 12:00:05.000 70

张三 2010-10-02 10:30:05.000 88

张三 2010-10-03 15:05:05.000 86

如果上面的 SQL ， 你不怎么看得懂， 那么下面这样的写法，与执行结果，应该能让你更加容易明白一些上面的SQL的处理的原理。

SELECT
name,
procDate,
result,
( SELECT
COUNT(1)
FROM
TopnTest subTopnTest
WHERE
TopnTest.name = subTopnTest.name
AND TopnTest.procDate < subTopnTest.procDate
) AS [有多少行数据处理时间比当前行大]
FROM
TopnTest
ORDER BY
name, procDate

name procDate result 有多少行数据处理时间比当前行大
---------- ----------------------- ----------- ---------------
李四 2010-10-01 06:00:05.000 60 4
李四 2010-10-04 08:00:05.000 90 3
李四 2010-10-05 10:00:05.000 75 2
李四 2010-10-08 11:00:05.000 88 1
李四 2010-10-09 12:00:05.000 60 0
王五 2010-09-10 08:00:05.000 70 4
王五 2010-09-14 08:00:05.000 80 3
王五 2010-09-25 18:00:05.000 75 2
王五 2010-09-28 18:00:05.000 88 1
王五 2010-10-09 12:00:05.000 70 0
张三 2010-10-01 12:00:05.000 80 4
张三 2010-10-01 12:20:05.000 85 3
张三 2010-10-02 07:25:15.000 79 2
张三 2010-10-02 10:30:05.000 88 1
张三 2010-10-03 15:05:05.000 86 0

(15 行受影响)