WHERE col1,col2 IN (...) [使用复合主键的 SQL 子查询] [英] WHERE col1,col2 IN (...) [SQL subquery using composite primary key]

问题描述

给定一个带有复合主键(a,b)的表foo，是否有编写查询的合法语法，例如:

Given a table foo with a composite primary key (a,b), is there a legal syntax for writing a query such as:

SELECT ... FROM foo WHERE a,b IN (SELECT ...many tuples of a/b values...);
UPDATE foo SET ... WHERE a,b IN (SELECT ...many tuples of a/b values...);

如果这是不可能的，并且您无法修改架构，您如何执行上述等效操作?

If this is not possible, and you could not modify the schema, how could you perform the equivalent of the above?

我还将在此处放置术语复合主键"、子选择"、子选择"和子查询"，以便在这些别名上搜索命中.

编辑:我对标准 SQL 以及适用于 PostgreSQL 和 SQLite 3 的答案感兴趣.

Edit: I'm interested in answers for standard SQL as well as those that would work with PostgreSQL and SQLite 3.

推荐答案

sqlite> create table foo (a,b,c);
sqlite> create table bar (x,y);
sqlite> select * from foo where exists (select 1 from bar where foo.a = bar.x and foo.b = bar.y);

将 select 1 from bar 替换为您的 select ... many tuples of a/b values ....

或者为您的 select ... 多个 a/b 值的元组 ... 创建一个临时表，并用它代替 bar..

Or create a temporary table of your select ... many tuples of a/b values ... and use it in place of bar..

这篇关于WHERE col1,col2 IN (...) [使用复合主键的 SQL 子查询]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！