SQL IN子句中的多列 [英] SQL multiple columns in IN clause

问题描述

如果我们需要基于给定列的一组值查询表，则可以简单地使用IN子句.

但是，如果需要基于多个列执行查询，则不能使用IN子句(SO线程中已包含).

从其他SO线程中，我们可以使用join或exist子句等规避此问题.但是，如果主表和搜索数据都在数据库中，它们都可以工作.

E.g
User table:
firstName, lastName, City

给出(名字，姓氏)元组的列表，我需要得到城市.

我可以想到以下解决方案.

1

构造一个选择查询，例如

SELECT city from user where (firstName=x and lastName=y) or (firstName=a and lastName=b) or .....

2

将所有firstName，lastName值上载到登台表中，并在"user"表和新登台表之间执行联接.

是否有解决此问题的选项，一般来说，解决此问题的首选方式是什么?

解决方案

您可以这样做:

SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));

sqlfiddle .. >

If we need to query a table based on some set of values for a given column, we can simply use the IN clause.

But if query need to be performed based on multiple columns, we could not use IN clause(grepped in SO threads.)

From other SO threads, we can circumvent this problem using joins or exists clause etc. But they all work if both main table and search data are in the database.

E.g
User table:
firstName, lastName, City

Given a list of (firstname, lastName) tuples, I need to get the cities.

I can think of following solutions.

1

Construct a select query like,

SELECT city from user where (firstName=x and lastName=y) or (firstName=a and lastName=b) or .....

2

Upload all firstName, lastName values into a staging table and perform a join between 'user' table and the new staging table.

Are there any options for solving this problem and what is the preferred of solving this problem in general?

解决方案

You could do like this:

SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));

The sqlfiddle.

这篇关于SQL IN子句中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！