基于DATE_CREATED列识别各NOTE_TYPE的最后使用时间（SQL问题）

我正在处理一套记录客户档案每次添加笔记的数据集，每条笔记对应特定的NOTE_TYPE，需要识别每种NOTE_TYPE的最后使用时间。原表结构包含PERSON_ID、NOTE_TYPE、DATE_CREATED字段，示例数据如下：

PERSON_ID字段无关，期望得到每种NOTE_TYPE仅显示一次、对应最后使用DATE_CREATED的结果：

我是SQL新手，尝试了如下代码，但结果错误，例如某昨天使用的笔记类型返回的最后使用时间为2004年：

SELECT NOTE_TYPE, 
       DATE_CREATED
from ( SELECT NOTE_TYPE, 
              DATE_CREATED, 
              ROW_NUMBER() over (partition by NOTE_TYPE order by DATE_CREATED) as rn
      from CASE_NOTES
) t
where rn = 1
ORDER BY DATE_CREATED

求正确的实现方法。

你的代码为啥出错？ #

两个关键问题导致结果不符合预期：

1. 排序方向搞反了：你用order by DATE_CREATED是按日期从小到大升序排列，所以rn=1拿到的是每种NOTE_TYPE最早创建的记录，而不是最新的最后使用记录。
2. 日期字段类型的坑：如果DATE_CREATED是字符串格式（比如示例中的DD/MM/YYYY），直接按字符串排序会逻辑混乱——比如02/01/2022的首字符是0，25/06/2020首字符是2，字符串排序会认为前者更小，但实际2022年的日期比2020年晚，这就导致你拿到的是旧日期。

正确写法 #

写法1：修正窗口函数逻辑 #

把排序改成降序，同时先将字符串格式的日期转换为日期类型再排序（避免字符串排序的错误）：

SELECT NOTE_TYPE, 
       DATE_CREATED
from ( 
    SELECT NOTE_TYPE, 
           DATE_CREATED, 
           -- 按NOTE_TYPE分组，日期从新到旧排序，最新的记录rn=1
           ROW_NUMBER() over (
               partition by NOTE_TYPE 
               order by STR_TO_DATE(DATE_CREATED, '%d/%m/%Y') desc -- MySQL写法，其他数据库看注释
           ) as rn
    from CASE_NOTES
) t
where rn = 1
ORDER BY STR_TO_DATE(DATE_CREATED, '%d/%m/%Y') desc;

不同数据库转换字符串为日期的函数不同：

• SQL Server：CONVERT(DATE, DATE_CREATED, 103)
• PostgreSQL：TO_DATE(DATE_CREATED, 'DD/MM/YYYY')
• Oracle：TO_DATE(DATE_CREATED, 'DD/MM/YYYY')

写法2：用GROUP BY + MAX()更简洁 #

如果只需要每种NOTE_TYPE的最新日期，用分组聚合的方法更直接，不需要嵌套子查询：

-- MySQL示例
SELECT 
    NOTE_TYPE,
    DATE_FORMAT(MAX(STR_TO_DATE(DATE_CREATED, '%d/%m/%Y')), '%d/%m/%Y') AS DATE_CREATED
FROM CASE_NOTES
GROUP BY NOTE_TYPE
ORDER BY MAX(STR_TO_DATE(DATE_CREATED, '%d/%m/%Y')) desc;

这个写法先把字符串转成日期类型，取分组内最大的日期（也就是最新的），再转换回原来的字符串格式输出，结果完全符合你的需求。

内容的提问来源于stack exchange，提问作者Sco77ie