基于两个created_at列排序conversation表的SQL问题求助

针对你的需求——当created_at_two不为NULL时按该字段排序，否则按created_at_one排序，有两种简洁可靠的写法：

方法一：使用COALESCE函数 #

COALESCE函数的作用是返回传入参数中第一个非NULL的值，完美匹配你的排序逻辑：

SELECT created_at_one, created_at_two, message_one, message_two
FROM conversation
ORDER BY COALESCE(created_at_two, created_at_one);

方法二：使用CASE语句 #

如果需要更直观的逻辑表达，CASE语句也能实现相同效果，适合后续需要扩展复杂判断的场景：

SELECT created_at_one, created_at_two, message_one, message_two
FROM conversation
ORDER BY 
    CASE 
        WHEN created_at_two IS NOT NULL THEN created_at_two
        ELSE created_at_one
    END;

扩展：指定排序方向 #

如果需要升序（默认）或降序排序，直接在排序规则后添加ASC或DESC即可：

-- 降序排序示例
SELECT created_at_one, created_at_two, message_one, message_two
FROM conversation
ORDER BY COALESCE(created_at_two, created_at_one) DESC;

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