主页 / dba / 问题 / 302006
Accepted
Lev
Asked: 2021-11-02 23:23:29 +0800 CST

SQLite 按 SELECT IN 顺序返回行

  • 772

IN是否可以以与子句中传递的顺序相同的顺序返回行，例如

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

并且行返回为：

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

我发现的最接近的例子是 Postgres WITH ORDINALITY。

sqlite

2 个回答

  1. 假设json1扩展可用且已启用，您可以使用该json_each函数加入包含 ID 的 JSON 数组。

    json_each是一个表值函数，它为 JSON 数组（或对象）的每个值返回一行。

    返回的行json_each具有架构

    CREATE TABLE json_each(
    key ANY,             -- key for current element relative to its parent
    value ANY,           -- value for the current element
    type TEXT,           -- 'object','array','string','integer', etc.
    atom ANY,            -- value for primitive types, null for array & object
    id INTEGER,          -- integer ID for this element
    parent INTEGER,      -- integer ID for the parent of this element
    fullkey TEXT,        -- full path describing the current element
    path TEXT,           -- path to the container of the current row
    json JSON HIDDEN,    -- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start
);

    在这种情况下，只需要key和value列。

    该value列包含数组中每个项目的值，并且该key列包含该值的从零开始的索引。

    因此，鉴于 JSON 数组[5, 7, 1]调用json_each('[5, 7, 1]')将返回

    key  value
---  -----
0    5    
1    7    
2    1

    请注意，虽然文档不保证返回的行的顺序json_each将key始终包含正确的索引，因此可以用于排序。

    所以使用json_each你可以这样写你的查询：

    SELECT 
    st.id, st.filler
FROM some_table st
JOIN json_each('[5,7,1]') AS tab
WHERE st.id = tab.value
ORDER BY tab.key

    小提琴

    虽然此解决方案确实需要json1扩展，但在大多数环境中默认情况下扩展应该可用并启用。

    更重要的是，该解决方案不需要手动生成VALUES列表。相反，JSON 可以只是一个可以绑定到准备好的语句的参数。

    除了扩展之外，唯一的其他要求json1是某种工具或库来从列表生成 JSON 数组，但大多数环境应该已经提供了其中一个。

    如果不是，假设所有值都保证为数字，则可以使用普通字符串连接轻松生成数组。

    • 3
  2. Best Answer

    您可以按如下方式执行此操作 - 或查看下面的修改方法，可能更好（初始代码在 fiddle here上可用 -在此处修改 fiddle和第三种方法）：

    初始方法：

    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。在后一个小提琴中，我已经一步一步地完成了这些步骤 - 帮助我自己弄清楚了！

    • 2

相关问题