如何在MySQL中对ISO 8601时长列进行升序排序

你的问题出在字符串排序是按字符ASCII顺序，而实际时长的优先级是：日(D) < 周(W) < 月(M) < 年(Y)，同时同单位的数值需要按大小排序。以下是不同场景的解决方案：

通用方案（适用于所有SQL数据库） #

通过给不同单位分配优先级权重，再结合数值大小排序，完全匹配你期望的结果：

SELECT duration
FROM durations
ORDER BY
  -- 按单位优先级排序：D(1) < W(2) < M(3) < Y(4)
  CASE RIGHT(duration, 1)
    WHEN 'D' THEN 1
    WHEN 'W' THEN 2
    WHEN 'M' THEN 3
    WHEN 'Y' THEN 4
  END ASC,
  -- 同单位下按数值大小排序
  CAST(SUBSTRING(duration, 2, LENGTH(duration)-2) AS UNSIGNED) ASC;

注：如果是SQL Server，把LENGTH替换为LEN即可。

数据库原生支持方案（更简洁） #

如果使用支持ISO 8601时长转间隔类型的数据库，可以直接转换后排序：

PostgreSQL #

PostgreSQL原生支持将ISO 8601时长字符串转为INTERVAL类型，直接排序即可：

SELECT duration
FROM durations
ORDER BY CAST(duration AS INTERVAL) ASC;

SQL Server #

可以通过DATEADD将时长转换为具体日期差值来排序：

SELECT duration
FROM durations
ORDER BY
  CASE RIGHT(duration, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(SUBSTRING(duration, 2, LEN(duration)-2) AS INT), 0)
    WHEN 'W' THEN DATEADD(WEEK, CAST(SUBSTRING(duration, 2, LEN(duration)-2) AS INT), 0)
    WHEN 'M' THEN DATEADD(MONTH, CAST(SUBSTRING(duration, 2, LEN(duration)-2) AS INT), 0)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(SUBSTRING(duration, 2, LEN(duration)-2) AS INT), 0)
  END ASC;

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