如何在PostgreSQL中实现Kotlin风格的双行比较排序逻辑？

我有一个Kotlin排序算法，想要在PostgreSQL的ORDER BY语句中实现该逻辑。Kotlin代码如下：

private val comparator = Comparator<Person> { a, b ->
        if (a.x != null || b.x != null) {
            greaterThanOrEqualWithNull(a.x, b.x)
        } else greaterThanOrEqualWithNull(a.y, b.y)
    }

private fun greaterThanOrEqualWithNull(x: Int?, y: Int?): Int =
     if (x == y) 0 // 注：原代码中`b`应为笔误，修正为`y`
     else if (x == null) -1
     else if (y == null) 1
     else if (x > y) 1
     else -1

核心需求：数据库中的Person表有x、y两个字段，排序规则为：

• 所有x为空的行排在x不为空的行前面
• x为空的行按y升序排序
• x不为空的行按x升序排序

我尝试了如下SQL语句，但未达到预期效果：

ORDER BY COALESCE(x, y) ASC;

示例输入输出：

输入：

// person(x?, y)
Person(13, 2)
Person(10, 5)
Person(null, 3)
Person(null, 7)

输出：

// person(x?, y)
Person(null, 3)
Person(null, 7)
Person(10, 5)
Person(13, 2)

COALESCE(x, y)的问题在于会把x为空行的y值和x非空行的x值混在一起比较，无法区分两个分组的优先级。需要拆分排序条件，先区分分组再内部排序。

写法一（简洁版） #

ORDER BY 
    -- 第一优先级：x为空的行前置（FALSE在TRUE前）
    x IS NOT NULL ASC,
    -- 第二优先级：分组内分别按x/y升序
    COALESCE(x, y) ASC;

写法二（逻辑更明确版） #

ORDER BY
    -- 标记分组：x为空的组标记为0，非空组标记为1，0在前
    CASE WHEN x IS NULL THEN 0 ELSE 1 END ASC,
    -- 分组内排序：非空组用x，空组用y
    CASE WHEN x IS NOT NULL THEN x ELSE y END ASC;

逻辑说明 #

1. 第一个排序条件先把数据分成两组：x为空的组排在最前面，x非空的组排在后面。
2. 第二个排序条件在每组内部执行升序排序，完全匹配你的Kotlin比较器逻辑。

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