MariaDB [test]> select count(*) entries, year(date) y, month(date) m, day(date) d from dates group by y, m, d;
+---------+------+------+------+
| entries | y | m | d |
+---------+------+------+------+
| 1 | 2018 | 1 | 1 |
| 3 | 2018 | 1 | 2 |
| 2 | 2018 | 1 | 3 |
| 1 | 2018 | 2 | 1 |
| 1 | 2018 | 10 | 3 |
+---------+------+------+------+
5 rows in set (0.00 sec)
从这里开始情况会变得复杂。为了避免这种情况,我们创建了一个视图:
MariaDB [test]> create view counts_per_day as (select count(*) entries, year(date) y, month(date) m, day(date) d from dates group by y, m, d);
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> select * From counts_per_day;
+---------+------+------+------+
| entries | y | m | d |
+---------+------+------+------+
| 1 | 2018 | 1 | 1 |
| 3 | 2018 | 1 | 2 |
| 2 | 2018 | 1 | 3 |
| 1 | 2018 | 2 | 1 |
| 1 | 2018 | 10 | 3 |
+---------+------+------+------+
5 rows in set (0.00 sec)
有了这个观点,下一部分就简单多了,因为这是一个典型的最大n个组的问题:
MariaDB [test]> select entries, a.y, a.m, a.d from counts_per_day a inner join (select y, m, max(entries) max_entries from counts_per_day group by y, m) b on a.entries = max_entries and a.y = b.y and a.m = b.m;
+---------+------+------+------+
| entries | y | m | d |
+---------+------+------+------+
| 3 | 2018 | 1 | 2 |
| 1 | 2018 | 2 | 1 |
| 1 | 2018 | 10 | 3 |
+---------+------+------+------+
3 rows in set (0.00 sec)
1条答案
按热度按时间j13ufse21#
问题是您需要按天分组(每天的条目),然后按月分组(一个月内每天的最大条目数)。您可以在一个查询中实现这一点,但我认为没有有效且简单的方法来实现这一点,因为您不能多次使用派生表。以下是我的建议:
假设你有一张这样的table:
排成几行:
样品
group by
(每天输入):从这里开始情况会变得复杂。为了避免这种情况,我们创建了一个视图:
有了这个观点,下一部分就简单多了,因为这是一个典型的最大n个组的问题: