使用SELECT DISTINCT与ORDER BY时Prepared Statement报错排查

你遇到的核心问题是：当用Prepared Statement参数化orderBy时，PostgreSQL无法识别SELECT列表中的CASE表达式和ORDER BY中的CASE表达式是完全等价的。在pgAdmin中用字面量时，数据库能关联两个CASE都依赖同一个CTE变量，所以没问题；但换成参数?后，数据库会把这两个CASE视为独立的参数化表达式，哪怕逻辑完全一致，也不认为它们是同一个列，因此触发SELECT DISTINCT, ORDER BY expressions must appear in select list错误。

方案1：给SELECT中的CASE表达式起别名，ORDER BY直接引用别名 #

这是最简单的解决方案，让ORDER BY直接指向SELECT列表中已存在的列，完全符合DISTINCT的语法要求：

SELECT DISTINCT 
    ac.*,
    select_translation(ac.title_text_id, :languageCode) AS title,
    -- 给排序用的CASE表达式起别名
    CASE :orderBy
         WHEN 'title' THEN select_translation(ac.title_text_id, :languageCode)
         WHEN 'created_ts' THEN TO_CHAR(ac.created_ts, 'YYYY-MM-DD HH24:MI:SS:MS') -- 注意：MM是月份，MI才是分钟，之前的写法有错误
         WHEN 'expiry_date' THEN TO_CHAR(ac.expiry_date, 'YYYY-MM-DD')
         ELSE NULL
    END AS sort_key
FROM action_cards ac      
ORDER BY sort_key -- 直接引用SELECT列表中的别名

方案2：用CTE封装参数，保持原逻辑结构 #

和你在pgAdmin中的写法对齐，把参数传入CTE，让数据库能识别两个CASE依赖同一个变量，从而判定它们是关联表达式：

WITH vars AS (
   SELECT :orderBy AS sort_col -- 参数化传入排序字段
)
SELECT DISTINCT 
    ac.*,
    select_translation(ac.title_text_id, :languageCode) AS title,
    CASE vars.sort_col
         WHEN 'title' THEN select_translation(ac.title_text_id, :languageCode)
         WHEN 'created_ts' THEN TO_CHAR(ac.created_ts, 'YYYY-MM-DD HH24:MI:SS:MS')
         WHEN 'expiry_date' THEN TO_CHAR(ac.expiry_date, 'YYYY-MM-DD')
         ELSE NULL
    END
FROM vars, action_cards ac      
ORDER BY
    CASE vars.sort_col
         WHEN 'title' THEN select_translation(ac.title_text_id, :languageCode)
         WHEN 'created_ts' THEN TO_CHAR(ac.created_ts, 'YYYY-MM-DD HH24:MI:SS:MS')
         WHEN 'expiry_date' THEN TO_CHAR(ac.expiry_date, 'YYYY-MM-DD')
         ELSE NULL
    END

方案3：动态生成SQL（需防范注入） #

如果允许动态拼接SQL，可以直接将合法的排序字段拼入语句，但必须严格校验参数值，避免SQL注入：

// 第一步：严格校验排序字段的合法性，只允许预设值
List<String> allowedSortCols = Arrays.asList("title", "created_ts", "expiry_date");
String validOrderBy = allowedSortCols.contains(orderBy) ? orderBy : "title";

// 第二步：根据合法值生成对应的排序表达式
String sortExpression = switch(validOrderBy) {
    case "title" -> "select_translation(ac.title_text_id, :languageCode)";
    case "created_ts" -> "TO_CHAR(ac.created_ts, 'YYYY-MM-DD HH24:MI:SS:MS')";
    case "expiry_date" -> "TO_CHAR(ac.expiry_date, 'YYYY-MM-DD')";
    default -> "NULL";
};

// 第三步：拼接最终SQL
String sql = String.format("""
    SELECT DISTINCT 
        ac.*,
        select_translation(ac.title_text_id, :languageCode) AS title,
        %s AS sort_key
    FROM action_cards ac      
    ORDER BY sort_key
""", sortExpression);

// 执行查询
return jdbcTemplate.query(
        sql,
        new MapSqlParameterSource().addValue("languageCode", languageCode),
        new ActionCardRowMapper()
);

• 你之前的TO_CHAR格式串中HH24:MM是错误的，MM代表月份，分钟应该用MI，否则时间格式会出错，已在上述方案中修正。

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