在 sql in 子句中使用元组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1474964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using tuples in sql in clause
提问by mr calendar
Given a database like this:
给定一个这样的数据库：
BEGIN TRANSACTION;
CREATE TABLE aTable (
a STRING,
b STRING);
INSERT INTO aTable VALUES('one','two');
INSERT INTO aTable VALUES('one','three');
CREATE TABLE anotherTable (
a STRING,
b STRING);
INSERT INTO anotherTable VALUES('one','three');
INSERT INTO anotherTable VALUES('two','three');
COMMIT;
I would like to do something along the lines of
我想做一些类似的事情
SELECT a,b FROM aTable
WHERE (aTable.a,aTable.b) IN
(SELECT anotherTable.a,anotherTable.b FROM anotherTable);
To get the answer 'one','three', but I'm getting "near ",": syntax error"
要得到答案“一”，“三”，但我得到“接近”，“：语法错误”
Is this possible in any flavour of SQL? (I'm using sqlite)
这在任何风格的 SQL 中都有可能吗？（我正在使用sqlite）
Am I making a gross conceptual error? Or what?
我犯了一个严重的概念错误吗？或者是什么？
回答by Michael Buen
your code works if you do it in PostgreSQL or Oracle. on MS SQL, it is not supported
如果您在 PostgreSQL 或 Oracle 中执行，您的代码就可以工作。在 MS SQL 上，不支持
use this:
用这个：
SELECT a,b FROM aTable
WHERE
-- (aTable.a,aTable.b) IN -- leave this commented, it makes the intent more clear
EXISTS
(
SELECT anotherTable.a,anotherTable.b -- do not remove this too, perfectly fine for self-documenting code, i.e.. tuple presence testing
FROM anotherTable
WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
);
[EDIT]
[编辑]
sans the stating of intent:
无意图陈述：
SELECT a,b FROM aTable
WHERE
EXISTS
(
SELECT *
FROM anotherTable
WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
);
it's somewhat lame, for more than a decade, MS SQL still don't have first-class support for tuples. IN tuple construct is way more readable than its analogous EXISTS construct. btw, JOIN also works (tster's code), but if you need something more flexible and future-proof, use EXISTS.
这有点蹩脚，十多年来，MS SQL 仍然没有一流的元组支持。IN 元组结构比其类似的 EXISTS 结构更具可读性。顺便说一句，JOIN 也可以（tster 的代码），但是如果您需要更灵活和面向未来的东西，请使用 EXISTS。
[EDIT]
[编辑]
speaking of SQLite, i'm dabbling with it recently. yeah, IN tuples doesn't work
说到 SQLite，我最近正在涉足它。是的，IN 元组不起作用
回答by tster
you can use a join:
您可以使用连接：
SELECT aTable.a, aTable.b FROM aTable
JOIN anotherTable ON aTable.a = anotherTable.a AND aTable.b = anotherTable.b
回答by Snorex
Another alternative is to use concatenation to make your 2-tuple into a single field :
另一种选择是使用串联将您的 2 元组变成单个字段：
SELECT a,b FROM aTable
WHERE (aTable.a||'-'||aTable.b) IN
(SELECT (anotherTable.a || '-' || anotherTable.b FROM anotherTable);
...just be aware that bad things can happen if a or b contain the delimiter '-'
...请注意，如果 a 或 b 包含分隔符“-”，可能会发生不好的事情
