我刚刚尝试了一个与@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 | +--------+-----------------+------+
