英文:
order result of query by on of two columns
问题
我有这个表：
CREATE TABLE Foo
(
id_key INTEGER PRIMARY KEY,
key1 INTEGER NOT NULL,
seq_num1 INTEGER NOT NULL,
key2 INTEGER,
seq_num2 INTEGER,
data BLOB NOT NULL
);
而我想选择匹配 key1 或 key2 的 data，这很简单：
SELECT data
FROM Foo
WHERE (key1 = ? OR key2 = ?)
但是接下来，我想根据在表的行中匹配的内容 - key1 或 key2，对此查询的结果进行排序。
我认为这样的写法应该可以工作：
SELECT data
FROM Foo
WHERE (key1 = ? OR key2 = ?)
ORDER BY
CASE
WHEN key1 = ?
THEN seq_num1
ELSE seq_num2
END
但我不确定这是否是最佳解决方案？因为在这个查询中，我对 key1 进行了两次比较，而只需要比较一次。
我使用的是 sqlite3。
英文:
I have this table:
CREATE TABLE Foo
(
id_key INTEGER PRIMARY KEY,
key1 INTEGER NOT NULL,
seq_num1 INTEGER NOT NULL,
key2 INTEGER,
seq_num2 INTEGER,
data BLOB NOT NULL
);
And I want to select data where key1 or key2 match,
this is simple:
SELECT data
FROM Foo
WHERE (key1 = ? OR key2 = ?)
but then I want to order results of this query by seq_num1 or seq_num2, depending on what matched in the table's row - key1 or key2 respectively.
I think that something like this should work:
SELECT data
FROM Foo
WHERE (key1 = ? OR key2 = ?)
ORDER BY
CASE
WHEN key1 = ?
THEN seq_num1
ELSE seq_num2
END
but I'm not sure is this optimal solution? Because of in this query I compare key1 twice, while it should be enough compare once.
I use sqlite3.
答案1
得分: 1
这是否是您想要的？我不使用SQLite，但这个MySQL命令似乎被SQLite理解。
select
data, seq_num1 as seq
from Foo
where key1 = ?
union
select
data, seq_num2 as seq
from Foo
where key2 = ?
order by seq;
英文:
Could this be what you want? I don't use sqlite, but this mysql command seems to be understood by sqlite.
select
data, seq_num1 as seq
from Foo
where key1 = ?
union
select
data, seq_num2 as seq
from Foo
where key2 = ?
order by seq;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论