PostgreSQL如何忽略年份比较并排序日期（需用CASE实现）

表结构与数据 #

astronauts表包含name（姓名）和birth_d（出生日期）字段，现有数据如下：

需求说明 #

编写SQL查询，忽略出生日期的年份，仅按月份+日期对数据进行排序，且必须通过CASE语句实现。

错误尝试分析 #

你之前的代码存在三个核心问题：

1. SQL执行顺序限制：SELECT子句定义的别名month_day，无法在同层级的CASE语句中直接引用；
2. 格式不匹配：日期范围写为12-02（日-月），和TO_CHAR生成的MM-DD（月-日）格式冲突，导致条件判断完全错误；
3. 缺少排序逻辑：仅生成了LRanking字段，但未将其用于排序，无法实现需求。

错误代码如下：

SELECT *, TO_CHAR (birth_d, 'MM-DD') AS "month_day",
CASE WHEN month_day >= "01-01" AND month_day <= "15-01" THEN 'A'
     WHEN month_day >= "12-02" AND month_day <= "29-02" THEN 'E'
     ...
END as "LRanking"
FROM astronauts;

正确实现方案 #

以下是几种符合要求的实现方式，核心是通过CASE语句生成可用于排序的键值：

方案1：按月份+日期拆分排序 #

通过CASE分别处理月份和日期的排序权重，逻辑清晰：

SELECT name, birth_d
FROM astronauts
ORDER BY 
  -- 先按月份排序
  CASE 
    WHEN EXTRACT(MONTH FROM birth_d) = 1 THEN 1
    WHEN EXTRACT(MONTH FROM birth_d) = 2 THEN 2
    WHEN EXTRACT(MONTH FROM birth_d) = 3 THEN 3
    WHEN EXTRACT(MONTH FROM birth_d) = 4 THEN 4
    WHEN EXTRACT(MONTH FROM birth_d) = 5 THEN 5
    WHEN EXTRACT(MONTH FROM birth_d) = 6 THEN 6
    WHEN EXTRACT(MONTH FROM birth_d) = 7 THEN 7
    WHEN EXTRACT(MONTH FROM birth_d) = 8 THEN 8
    WHEN EXTRACT(MONTH FROM birth_d) = 9 THEN 9
    WHEN EXTRACT(MONTH FROM birth_d) = 10 THEN 10
    WHEN EXTRACT(MONTH FROM birth_d) = 11 THEN 11
    WHEN EXTRACT(MONTH FROM birth_d) = 12 THEN 12
  END,
  -- 同一月份内按日期排序
  CASE 
    WHEN EXTRACT(DAY FROM birth_d) BETWEEN 1 AND 31 THEN EXTRACT(DAY FROM birth_d)
  END;

方案2：生成可排序的月日字符串 #

利用MM-DD格式字符串的字典序与日期顺序一致的特性，通过CASE生成标准格式后排序：

SELECT name, birth_d,
  TO_CHAR(birth_d, 'MM-DD') AS month_day
FROM astronauts
ORDER BY
  CASE
    WHEN birth_d IS NOT NULL THEN TO_CHAR(birth_d, 'MM-DD')
  END;

方案3：转换为整数排序（更简洁） #

将月日拼接为MMDD格式的整数，通过CASE返回该整数进行排序：

SELECT name, birth_d
FROM astronauts
ORDER BY
  CASE
    WHEN birth_d IS NOT NULL THEN TO_CHAR(birth_d, 'MMDD')::INT
  END;

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