クエリを選択して、最大日付で重複する値を表示します

これには、GROUPBYとHAVINGを使用します。まずテーブルを作成しましょう-

mysql> create table DemoTable
   (
   StudentName varchar(100),
   DueDate date
   );
Query OK, 0 rows affected (0.72 sec)

例

挿入コマンド-

mysql> insert into DemoTable values('John','2019-01-11');
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable values('Chris','2019-02-11');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('Chris','2019-03-11');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('John','2019-04-11');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Bob','2019-05-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Bob','2019-06-11');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Robert','2019-07-11');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('Robert','2019-08-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Robert','2019-09-11');
Query OK, 1 row affected (0.15 sec)

selectステートメントを使用してテーブルのすべてのレコードを表示する-

mysql> select *from DemoTable;

出力

+-------------+------------+
| StudentName | DueDate    |
+-------------+------------+
| John        | 2019-01-11 |
| Chris       | 2019-02-11 |
| Chris       | 2019-03-11 |
| John        | 2019-04-11 |
| Bob         | 2019-05-11 |
| Bob         | 2019-06-11 |
| Robert      | 2019-07-11 |
| Robert      | 2019-08-11 |
| Robert      | 2019-09-11 |
+-------------+------------+
9 rows in set (0.00 sec)

以下は、最大日付で重複する値を表示するためのクエリです-

mysql> select tbl.StudentName,max(tbl.DueDate) from DemoTable tbl group by tbl.StudentName
having count(*) > 1;

出力

+-------------+------------------+
| StudentName | max(tbl.DueDate) |
+-------------+------------------+
| John        | 2019-04-11       |
| Chris       | 2019-03-11       |
| Bob         | 2019-06-11       |
| Robert      | 2019-09-11       |
+-------------+------------------+
4 rows in set (0.00 sec)