There is a contest start from Jan 01, 2018 and end at Feb 01, 2018.
There are 2 tables:
members:
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| name | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
submissions:
+-----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| member_id | bigint(20) | NO | | NULL | |
| submission_date | date | NO | | NULL | |
| score | int(11) | NO | | NULL | |
+-----------------+------------+------+-----+---------+-------+
Write a query to find the member_id and name of the member who made highest score each day, among the members who made at least 1 submission each day (starting from Jan 01, 2018). If more than one such member have same score, print the one who has lowest member_id. The result should be this information for each day of the contest, sorted by the date.
Sample Data:
members:
+----+--------+
| id | name |
+----+--------+
| 1 | Amy |
| 2 | Tony |
| 3 | Rose |
| 4 | Bonnie |
| 5 | Carol |
| 6 | Tad |
| 7 | Garret |
| 8 | Earl |
| 9 | Jake |
| 10 | Rabbie |
+----+--------+
submissions:
+----+-----------+-----------------+-------+
| id | member_id | submission_date | score |
+----+-----------+-----------------+-------+
| 1 | 1 | 2018-01-01 | 10 |
| 2 | 2 | 2018-01-01 | 0 |
| 3 | 3 | 2018-01-01 | 100 |
| 4 | 5 | 2018-01-01 | 30 |
| 5 | 7 | 2018-01-01 | 50 |
| 6 | 8 | 2018-01-01 | 50 |
| 7 | 9 | 2018-01-01 | 100 |
| 8 | 10 | 2018-01-01 | 10 |
| 9 | 1 | 2018-01-02 | 100 |
| 10 | 2 | 2018-01-02 | 30 |
| 11 | 3 | 2018-01-02 | 20 |
| 12 | 5 | 2018-01-02 | 50 |
| 13 | 7 | 2018-01-02 | 100 |
| 14 | 9 | 2018-01-02 | 40 |
| 15 | 2 | 2018-01-03 | 20 |
| 16 | 2 | 2018-01-03 | 40 |
| 17 | 4 | 2018-01-03 | 100 |
| 18 | 7 | 2018-01-03 | 0 |
| 19 | 9 | 2018-01-03 | 30 |
| 20 | 2 | 2018-01-04 | 20 |
| 21 | 3 | 2018-01-04 | 100 |
| 22 | 4 | 2018-01-04 | 30 |
| 23 | 7 | 2018-01-04 | 60 |
| 24 | 8 | 2018-01-04 | 70 |
| 25 | 2 | 2018-01-05 | 80 |
| 26 | 4 | 2018-01-05 | 20 |
| 27 | 5 | 2018-01-05 | 0 |
| 28 | 7 | 2018-01-05 | 100 |
| 29 | 10 | 2018-01-05 | 20 |
+----+-----------+-----------------+-------+
Sample Result:
+-----------------+-----------+--------+-------+
| submission_date | member_id | name | score |
+-----------------+-----------+--------+-------+
| 2018-01-01 | 3 | Rose | 100 |
| 2018-01-02 | 1 | Amy | 100 |
| 2018-01-03 | 2 | Tony | 40 |
| 2018-01-04 | 7 | Garret | 60 |
| 2018-01-05 | 7 | Garret | 100 |
+-----------------+-----------+--------+-------+
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/95221.html
標籤：MySQL
上一篇：MySQL如何修改密碼
下一篇：because another server process exited abnormally and possibly corrupted shared m