我刚刚尝试了一个与@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_month
ORDER 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.hits
FROM my_data_per_month m1
LEFT OUTER JOIN my_data_per_month m2
ON (m1.month = m2.month AND m1.hits < m2.hits)
GROUP BY m1.month, m1.ip_address
HAVING COUNT(*) < 3
ORDER 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 |
+--------+-----------------+------+
602392714
清零编程群