我想保留使用 UNION 收集列值的子查询中的列顺序（改进我在这里提出的问题SQLite 替代转置/子选择回传 N 列 - 预期 1）。下面的查询按键的升序对数字进行排序，我想不出一种方法来保留Keys表中的列顺序而不引入另一个属性。

一个最小的例子：

CREATE TABLE Keys(primary_id, id1, id2, id3);
CREATE TABLE Attr(id, attr1, attr2);

INSERT INTO Keys VALUES(1, 7, 2, 5);
INSERT INTO Keys VALUES(2, 6, 1, 3);
INSERT INTO Keys VALUES(3, 4, 2, 7);
INSERT INTO Attr VALUES(1, "a", "b");
INSERT INTO Attr VALUES(2, "c", "d");
INSERT INTO Attr VALUES(3, "e", "f");
INSERT INTO Attr VALUES(4, "g", "h");
INSERT INTO Attr VALUES(5, "i", "j");
INSERT INTO Attr VALUES(6, "k", "l");
INSERT INTO Attr VALUES(7, "m", "n");

SELECT *
FROM Attr
WHERE Attr.id IN (SELECT primary_id FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id1 FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id2 FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id3 FROM Keys WHERE Keys.primary_id=2);

1|a|b
2|c|d
3|e|f
6|k|l

保留以下顺序的所需输出primary_id, id1, id2, id3：

2|c|d
6|k|l
1|a|b
3|e|f

子查询本身我可以通过创建另一个属性来排序：

SELECT primary_id, 1 as sort FROM Keys WHERE Keys.primary_id = 2
UNION
SELECT id1, 2 as sort FROM Keys WHERE Keys.primary_id = 2
UNION
SELECT id2, 3 as sort FROM Keys WHERE Keys.primary_id = 2
UNION
SELECT id3, 4 as sort FROM Keys WHERE Keys.primary_id = 2
ORDER BY sort

但是外部查询只需要 1 列，因此这不是解决方案。谢谢你的想法！

uj5u.com热心网友回复：

使用回传 2 列的 CTE 并将其连接到表中，而不是使用 operator IN。
然后您可以使用该列sort对结果进行排序：

WITH cte AS (
  SELECT primary_id AS id, 1 AS sort FROM Keys WHERE Keys.primary_id = 2
  UNION 
  SELECT id1, 2 AS sort FROM Keys WHERE Keys.primary_id = 2
  UNION 
  SELECT id2, 3 AS sort FROM Keys WHERE Keys.primary_id = 2
  UNION 
  SELECT id3, 4 AS sort FROM Keys WHERE Keys.primary_id = 2
)
SELECT DISTINCT a.*
FROM Attr a INNER JOIN cte c
ON c.id = a.id
ORDER BY c.sort;

请参阅演示。

uj5u.com热心网友回复：

您可以加入一个IN.
并按CASE.

关于db<>fiddle 的演示在这里