首先，我们可以使用以下子查询来获取每个ID及其最新日期：

SELECT id, MAX(date) AS maxDate
FROM yourtable
GROUP BY id;

这基本上可以在整个查询中使用，如下所示：

SELECT y.id, y.status, y.role, 
FORMAT(y.date, 'MMM dd yyyy') AS date
FROM yourtable y
JOIN
(SELECT id, MAX(date) AS maxDate
FROM yourtable
GROUP BY id) grouped
ON y.id = grouped.id
AND y.date = grouped.maxDate
ORDER BY y.id;

但这将同时列出“蓝色”和“红色”角色，以防它们具有相同的最新日期。
因此，结果可能不正确：
| 识别号|状态|角色|日期|
| - ------|- ------|- ------|- ------|
| 1个|主动|红色|二零二二年十二月二十日|
| 第二章|主动|蓝色|2022年2月2日|
| 第二章|主动|红色|2022年2月2日|
| 三个|未激活|蓝色|2022年12月3日|
| 四个|主动|红色|2022年12月4日|
因此，在这种情况下，为了满足只显示“blue”行的条件，有不同的选择，其中之一是使用带有窗口函数的进一步的子查询，例如ROW_NUMBER。
如果有更多的作用，这可能是必要的。
在我们只有两个角色的特定情况下，我们不需要这样做，但可以使用MIN，因为“blue”出现在“red”之前（如果我们希望得到“red”行，则使用MAX）。
所以现在的查询是：

SELECT y.id, y.status, 
MIN(y.role) AS role, 
FORMAT(y.date, 'MMM dd yyyy') AS date
FROM yourtable y
JOIN
(SELECT id, MAX(date) AS maxDate
FROM yourtable
GROUP BY id) grouped
ON y.id = grouped.id
AND y.date = grouped.maxDate
GROUP BY y.id, y.status, y.date
ORDER BY y.id;

这将产生正确的结果：
| 识别号|状态|角色|日期|
| - ------|- ------|- ------|- ------|
| 1个|主动|红色|二零二二年十二月二十日|
| 第二章|主动|蓝色|2022年2月2日|
| 三个|未激活|蓝色|2022年12月3日|
| 四个|主动|红色|2022年12月4日|
我们可以在这里复制：db<>fiddle
一般提示：如果可能，我们应该避免使用SQL关键字作为列名或表名（这里是“角色”和“日期”）。
特别是“日期”这个名字也没有意义，因为它没有告诉我们是哪种日期。因此，我们应该更喜欢明确的名字，例如“sellDate”或“quittingDate”。