在 SQL in 子句中使用元组
给定一个这样的数据库:
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;
我想做一些类似的事情来
SELECT a,b FROM aTable
WHERE (aTable.a,aTable.b) IN
(SELECT anotherTable.a,anotherTable.b FROM anotherTable);
得到答案'一','三',但我得到“接近”,“:语法错误”
这在任何形式的SQL中都可能吗? (我正在使用 SQLite)
我犯了一个严重的概念错误吗?或者什么?
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)
Am I making a gross conceptual error? Or what?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
如果您在 PostgreSQL 或 Oracle 中执行,您的代码就可以工作。在 MS SQL 上,不支持
使用以下内容:
[编辑]
没有意图说明:
这有点蹩脚,十多年来,MS SQL 仍然没有对元组提供一流的支持。 IN 元组构造比其类似的 EXISTS 构造更具可读性。顺便说一句,JOIN 也可以(tster 的代码),但如果您需要更灵活且面向未来的东西,请使用 EXISTS。
[编辑]
说到 SQLite,我最近正在涉足它。是的,IN 元组不起作用
your code works if you do it in PostgreSQL or Oracle. on MS SQL, it is not supported
use this:
[EDIT]
sans the stating of intent:
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.
[EDIT]
speaking of SQLite, i'm dabbling with it recently. yeah, IN tuples doesn't work
您可以使用联接:
you can use a join:
另一种选择是使用串联将 2 元组变成单个字段:
...请注意,如果 a 或 b 包含分隔符“-”,则可能会发生不好的事情
Another alternative is to use concatenation to make your 2-tuple into a single field :
...just be aware that bad things can happen if a or b contain the delimiter '-'