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

问题描述

如果我们需要根据给定列的某些值集查询表，我们可以简单地使用 IN 子句.

但是如果需要基于多列执行查询，我们不能使用IN子句(在SO线程中grepped.)

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

例如用户表:名字，姓氏，城市

给定一个 (firstname, lastName) 元组列表，我需要获取城市.

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

1

构造一个选择查询，如，

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

2

将所有 firstName、lastName 值上传到临时表中，并在用户"表和新临时表之间执行连接.

是否有解决此问题的任何选项?一般而言，解决此问题的首选方法是什么?

解决方案

你可以这样做:

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.

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