在Postgres中使用WHERE...ANY
样式查询并按给定数组排序时,我是否遗漏了什么技巧?例如:
SELECT *
FROM table
WHERE id = ANY (<idsInDesiredOrder>)
在Postgres中使用WHERE...ANY
样式查询并按给定数组排序时,我是否遗漏了什么技巧?例如:
SELECT *
FROM table
WHERE id = ANY (<idsInDesiredOrder>)
WITH sample (id, description) AS (
VALUES
(1, 'lorem ipsum'),
(2, 'lorem ipsum'),
(3, 'lorem ipsum'),
(4, 'lorem ipsum')
)
SELECT
*
FROM
sample
WHERE
id = ANY (ARRAY[3,2])
ORDER BY
array_position(ARRAY[3,2], id);
ORDER BY
子句,SQL永远不会保证排序。历史上,在Postgres中这样的情况很棘手,因为您无法编写引用数组索引的ORDER BY
。WITH ORDINALITY
子句来解决了这个问题,允许您将数组拆分成“值”列和“索引”列:SELECT * FROM table
JOIN UNNEST(<preSortedIds>) WITH ORDINALITY u(id,pos) USING (id)
ORDER BY pos
SELECT *
FROM table
WHERE id = ANY (<preSortedIds>)
ORDER BY id