取得分组 TOP-N

来源：互联网发布：s7-200中断编程编辑：程序博客网时间：2024/04/28 15:12

前几天遇到这个问题，在网上搜索了一些资料如下，数据少的时候很好，但是数据量大的时候，速度有点慢，酌情处理吧

一、use Northwind

Select *
FROM orders A
Where EXISTS
          (Select COUNT(1)
         FROM orders
         Where employeeid = A.employeeid AND orderid > A.orderid
         HAVING COUNT(1) < 3)
ORDER BY EmployeeID

语义：在库Northwind中查询表Orders里按EmployeeID分组，每个EmployeeID取前三条OrderID最大的记录

二、

百度知道玩多了，常常会发现很多自己不知道的问题，留意一下别人是如何解决的。长长见识。

今天开始，就折腾个SQL查询案例的分组，收集一些要求比较奇怪的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-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 行受影响)

转载地址：http://blog.163.com/wm_blue/blog/static/112775329201232544430393/

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

下面是我自己设计的数据库并实现：

CREATE TABLE `test3` (
`report_date` int(11) NOT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据：

insert into test3 values(1,10);

insert into test3 values(1,9);

insert into test3 values(1,8);

insert into test3 values(1,7);

insert into test3 values(1,6);

select * from test3 a where (
SELECT count(1) FROM test3 b where a.num < b.num and a.report_date=b.report_date
) <2 order by report_date,num desc

分布理解该语句：

外层select就像是for循环，先扫描第一行：

SELECT * FROM test3 a , test3 b where a.num=10 and a.num < b.num and a.report_date=b.report_date

自身对比，发现没有比10更大的，那么就没有输出结果，那么count()自然为0，满足<2的条件，则第一条num为10的符合，

下面进行扫面第二行：

SELECT * FROM test3 a , test3 b where a.num=9 and a.num < b.num and a.report_date=b.report_date

输出结果'1', '9', '1', '10'
只有一个比她大，就是10.那个count()结果是1<2符合条件，该行输出，

继续如此操作，直到扫描结束。

0 0