按MySQL中的前N个分组

我刚刚尝试了一个与@Charles
Bretana给出的查询非常相似的查询，它确实起作用。我使用了VIEW来帮助澄清问题。

CREATE TABLE my_data ( my_date DATE, ip_address CHAr(15));

插入一堆日期/ IP地址对（未显示）…

为每月所有计数和IP地址创建一个视图：

CREATE VIEW my_data_per_month as SELECt EXTRACT(YEAR_MonTH FROM my_date) AS month,   ip_address, COUNT(*) AS hits FROM my_data GROUP BY month, ip_address;SELECt * FROM my_data_per_monthORDER BY month ASC, hits DESC;+--------+-----------------+------+| month  | ip_address      | hits |+--------+-----------------+------+| 200901 | 999.999.999.999 |    8 | | 200901 | 999.999.999.998 |    6 | | 200901 | 999.999.999.997 |    5 | | 200901 | 999.999.999.996 |    4 | | 200901 | 999.999.999.995 |    3 | | 200901 | 999.999.999.994 |    2 | | 200902 | 999.999.999.998 |    8 | | 200902 | 999.999.999.997 |    6 | | 200902 | 999.999.999.996 |    5 | | 200902 | 999.999.999.995 |    4 | | 200902 | 999.999.999.994 |    3 | | 200902 | 999.999.999.993 |    2 | | 200903 | 999.999.999.997 |    8 | | 200903 | 999.999.999.996 |    6 | | 200903 | 999.999.999.995 |    5 | | 200903 | 999.999.999.994 |    4 | | 200903 | 999.999.999.993 |    3 | | 200903 | 999.999.999.992 |    2 | +--------+-----------------+------+

现在显示每月前三个IP地址：

SELECt m1.month, m1.ip_address, m1.hitsFROM my_data_per_month m1LEFT OUTER JOIN my_data_per_month m2  ON (m1.month = m2.month AND m1.hits < m2.hits)GROUP BY m1.month, m1.ip_addressHAVINg COUNT(*) < 3ORDER BY m1.month ASC, m1.hits DESC;+--------+-----------------+------+| month  | ip_address      | hits |+--------+-----------------+------+| 200901 | 999.999.999.999 |    8 | | 200901 | 999.999.999.998 |    6 | | 200901 | 999.999.999.997 |    5 | | 200902 | 999.999.999.998 |    8 | | 200902 | 999.999.999.997 |    6 | | 200902 | 999.999.999.996 |    5 | | 200903 | 999.999.999.997 |    8 | | 200903 | 999.999.999.996 |    6 | | 200903 | 999.999.999.995 |    5 | +--------+-----------------+------+