The table structure is as follows:
CREATE TABLE ' Dwb_rmirror_req_d ' (
' thedate ' varchar (+) not NULL DEFAULT ' ',
' node ' varchar (not NULL DEFAULT ' '),
' Req_num ' bigint () DEFAULT NULL,
PRIMARY KEY (' thedate ', ' node ')
) Engine=innodb DEFAULT Charset=utf8
The records in the table are as follows:
Mysql> select * from Dwb_rmirror_req_d;
+----------+------+------------+
| Thedate | Node | Req_num |
+----------+------+------------+
| 20160215 | f | 2 |
| 20160215 | I | 1 |
| 20160215 | l | 3 |
| 20160217 | f | 2 |
| 20160217 | I | 1 |
| 20160217 | l | 3 |
| 20160218 | f | 2 |
| 20160218 | I | 1 |
| 20160218 | l | 3 |
| 20160219 | f | 2 |
| 20160219 | I | 1 |
| 20160219 | l | 3 |
| 20160220 | f | 2 |
| 20160220 | I | 1 |
| 20160220 | l | 3 |
| 20160221 | f | 2 |
| 20160221 | I | 1 |
| 20160221 | l | 3 |
+----------+------+------------+
Rows in Set (0.00 sec)
1. Get the maximum number of queries per day:
Select A.thedate,a.node,a.req_num from Dwb_rmirror_req_d a LEFT join Dwb_rmirror_req_d b
on A.thedate = b.thedate and a.req_num <= b.req_num
Group by A.thedate,a.node,a.req_num
have count (b.node) <= 1; The
results are as follows:
+----------+------+------------+
| thedate | node | req_num |
+----------+------+------------+
| 20160215 | l | 3 |
| 20160217 | l | 3 |
| 20160218 | l | 3 |
| 20160219 | l | 3 |
| 20160220 | l | 3 |
| 20160221 | l | 3 |
+----------+------+------------+
6 rows in Set (0.01 sec)
2. Get the two records with the highest number of queries per day:
Select A.thedate,a.node,a.req_num from Dwb_rmirror_req_d a LEFT join Dwb_rmirror_req_d b
On a.thedate = B.thedate and A.req_num <= b.req_num
GROUP BY A.thedate,a.node,a.req_num
Having count (B.node) <=2
Order BY A.thedate,a.req_num;
The results are as follows:
+----------+------+------------+
| Thedate | Node | Req_num |
+----------+------+------------+
| 20160215 | f | 2 |
| 20160215 | l | 3 |
| 20160217 | f | 2 |
| 20160217 | l | 3 |
| 20160218 | f | 2 |
| 20160218 | l | 3 |
| 20160219 | f | 2 |
| 20160219 | l | 3 |
| 20160220 | f | 2 |
| 20160220 | l | 3 |
| 20160221 | f | 2 |
| 20160221 | l | 3 |
+----------+------+------------+
Rows in Set (0.01 sec)
MySQL takes the first N records in a group