SQLite 按 SELECT IN 順序返回行

IN是否可以以與子句中傳遞的順序相同的順序返回行，例如

SELECT * FROM some_table WHERE id IN (5, 7, 1);

並且行返回為：

id |     value
--------------------
5 | something
7 | something else
1 | some value

我發現的最接近的例子是 Postgres WITH ORDINALITY。

您可以按如下方式執行此操作 - 或查看下面的修改方法，可能更好（初始程式碼可在 fiddle here - 修改 fiddle here和第三種方法here）：

初始方法：

CREATE TABLE some_table
(
 id INT NOT NULL PRIMARY KEY,
 filler TEXT NOT NULL
);

填充：

INSERT INTO some_table VALUES 
(1,   '1_val'),
(2,   '2_val'),
(3,   '3_val'),
(4,   '4_val'),
(5,   '5_val'),
(6,   '6_val'),
(7,   '7_val'),
(8,   '8_val'),
(9,   '9_val'),
(10, '10_val');

然後執行以下 SQL：

SELECT 
 st.id, st.filler
FROM some_table st 
WHERE id IN (5, 7, 1)
ORDER BY 
 CASE st.id
   WHEN 5 THEN 1
   WHEN 7 THEN 2
   WHEN 1 THEN 3
 END ;

結果：

id  filler
5   5_val
7   7_val
1   1_val

使用 ORDER 的修訂方法（目前版本）：

您還可以執行以下操作 - 如果您有一長串值可能會更好？

SELECT
 st.id, st.filler, tab.rn, tab.column1
FROM some_table st
JOIN
(
 SELECT 
   ROW_NUMBER() OVER () AS rn, column1 
   FROM (VALUES (5), (7), (1))
) AS tab
ON st.id = tab.column1
ORDER BY tab.rn;

結果：

id  filler  rn  column1
5   5_val   1        5
7   7_val   2        7
1   1_val   3        1

當然，您可以不選擇rnandcolumn1列來獲得您想要的結果 - 請參閱修改後的小提琴！

不幸的是，從這裡開始，SQLite 不允許您為從VALUES子句構造的表起別名（請參閱此處）- PostgreSQL確實允許這樣做- 請參閱此處。

筆記！

對 SQLite 郵件列表的討論表明，這是未記錄在案的，並且不能依賴於未來 - 但這是目前版本 (3.36) 之前的所有版本的行為。我建議保留並記錄該行為 - 到目前為止沒有回复 - 在技術上變得複雜……

保證訂單（現在和未來）

在 SQLite 列表上發布後，目前的播放狀態似乎是，正如@DavidSpillett 所說，VALUES子句中的順序不能保證被傳播到SELECT由該子句產生的任何表上的任何一個VALUES- 到未來。在撰寫本文時（2021 年 11 月 3 日），將遵守 VALUES 子句的順序（直到 3.36 版），但未來不能保證。

所以，你必須明確地用你的數據下訂單（見小提琴）：

SELECT
 st.id, st.filler, tab.rn, tab.column1, tab.column2
FROM some_table st
JOIN
(
 SELECT 
   ROW_NUMBER() OVER () AS rn, x.column1, x.column2 
   FROM (VALUES (1, 5), (2, 7), (3, 1)) AS x
) AS tab
ON st.id = tab.column2
ORDER BY tab.column1;

結果（省略不需要的欄位）：

id   filler     rn  column1     column2
5    5_val      1        1           5
7    7_val      2        2           7
1    1_val      3        3           1

另一種保證訂購方式

我在這裡找到了這個巧妙的小“技巧” ​​（參見這裡的小提琴）。這就是我所說的…"poor man's recursion"

SELECT 
 id,
 filler, 
 INSTR(',5,7,1,)', ',' || id || ',') AS stuff
FROM some_table
WHERE stuff > 0
ORDER BY stuff;

結果 - 與其他相同。

欄位值之前的逗號意味著，例如，111在您搜尋時不會被選中11- SQLite 執行隱式CAST- 但這不應該被依賴。PostgreSQL 做同樣的事情（參見這裡的小提琴），即CAST不需要 ing。在後一個小提琴中，我已經一步一步地完成了這些步驟 - 幫助我自己弄清楚了！

引用自：https://dba.stackexchange.com/questions/302006