如何在ClickHouse中按IN子句内指定的顺序返回查询结果？

针对你需要按IN子句内指定的id顺序返回查询结果的需求，ClickHouse可以通过以下几种方式实现类似MySQL ORDER BY FIELD()的效果：

方法1：使用arrayPosition函数（推荐） #

利用数组位置函数，将目标id列表转为数组，通过每个id在数组中的位置来排序：

SELECT name
FROM table
WHERE id IN (5,3,7,8,11,14,54)
ORDER BY arrayPosition([5,3,7,8,11,14,54], id)

arrayPosition会返回元素在数组中的索引位置（从1开始），按这个位置排序就能严格遵循你指定的id顺序。

方法2：使用CASE WHEN映射排序权重 #

如果目标id数量较少，可以直接给每个id分配对应的排序优先级：

SELECT name
FROM table
WHERE id IN (5,3,7,8,11,14,54)
ORDER BY 
    CASE id
        WHEN 5 THEN 1
        WHEN 3 THEN 2
        WHEN 7 THEN 3
        WHEN 8 THEN 4
        WHEN 11 THEN 5
        WHEN 14 THEN 6
        WHEN 54 THEN 7
    END

每个id对应的数字越小，排序越靠前，完全匹配你指定的顺序。

方法3：关联临时数据集（适合大量id的场景） #

当需要排序的id数量很多时，可以用WITH子句生成带排序顺序的临时数据，再与原表关联排序：

WITH (
    SELECT arrayJoin([(1,5), (2,3), (3,7), (4,8), (5,11), (6,14), (7,54)]) AS (sort_order, id)
) AS temp_order
SELECT t.name
FROM table t
JOIN temp_order ON t.id = temp_order.id
ORDER BY temp_order.sort_order

通过arrayJoin将包含排序顺序和id的数组展开为临时表，关联后按临时表的sort_order字段排序即可。

内容的提问来源于stack exchange，提问作者Артём Пучков