我想保留使用 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.
SELECT a.* FROM Attr a INNER JOIN Keys k ON k.primary_id = 2 AND a.id IN (k.primary_id, k.id1, k.id2, k.id3) ORDER BY CASE a.id WHEN k.primary_id THEN 0 WHEN k.id1 THEN 1 WHEN k.id2 THEN 2 WHEN k.id3 THEN 3 ELSE 9 END
| ID | 屬性1 | 屬性2 |
|---|---|---|
| 2 | C | d |
| 6 | 克 | 升 |
| 1 | 一個 | 乙 |
| 3 | 電子 | F |
關于db<>fiddle 的演示在這里
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/403605.html
標籤：