使用 SQLite WHERE 子句和 IN 运算符时保持顺序 [英] Maintain order when using SQLite WHERE-clause and IN operator

问题描述

考虑以下 tbl:

CREATE TABLE tbl (ID INTEGER, ticker TEXT, desc TEXT);

INSERT INTO tbl (ID, ticker, desc) 
    VALUES (1, 'GDBR30', '30YR'),
           (2, 'GDBR10', '10YR'),
           (3, 'GDBR5', '5YR'),
           (4, 'GDBR2', '2YR');

作为参考，tbl 看起来像这样:

For reference, tbl looks like this:

ID   ticker   desc
1    GDBR30   30YR
2    GDBR10   10YR
3    GDBR5    5YR
4    GDBR2    2YR

发出以下语句时，结果将根据ID排序.

When issuing the following statement, the result will be ordered according to ID.

SELECT * FROM tbl
WHERE ticker in ('GDBR10', 'GDBR5', 'GDBR30')

ID   ticker   desc
1    GDBR30   30YR
2    GDBR10   10YR
3    GDBR5    5YR

但是，我需要按照传递的值列表的顺序进行排序.这是我要找的:

However, I need the ordering to adhere to the order of the passed list of values. Here's what I am looking for:

ID   ticker   desc
2    GDBR10   10YR
3    GDBR5    5YR
1    GDBR30   30YR

推荐答案

您可以创建一个 CTE 返回 2 列:您搜索的值和每个值的排序顺序并加入它到桌子上.
在 ORDER BY 子句中使用排序顺序列对结果进行排序:

You can create a CTE that returns 2 columns: the values that you search for and for each value the sort order and join it to the table.
In the ORDER BY clause use the sort order column to sort the results:

WITH cte(id, ticker) AS (VALUES (1, 'GDBR10'), (2, 'GDBR5'), (3, 'GDBR30'))
SELECT t.* 
FROM tbl t INNER JOIN cte c
ON c.ticker = t.ticker
ORDER BY c.id

请参阅演示.

这篇关于使用 SQLite WHERE 子句和 IN 运算符时保持顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！