新人MySQL面试题求助

[南洋老司机]

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 |

+-----------------+-----------+--------+-------+