子查询中where子句中的两列 [英] Two columns in subquery in where clause

问题描述

我有一个查询：

SELECT s.period, s.year, s.amount 
FROM salaries s

我想从薪水中选择仅列出周期和年份等于的行：

I would like to select from salaries table only the rows that have period and year equal to:

SELECT p.period, p.years 
FROM periods p

我知道最简单的方法是使用连接，但问题是-在应用程序中我只能在 WHERE

I know that the simplest way would be use join, but the problem is - that in the application I can add only a clause after WHERE

之后添加一个子句，因此解决方案应该是：

So the solution should be:

SELECT s.period, s.year, s.amount 
FROM salaries s 
WHERE ...

这可能吗？

编辑：
结果应与以下内容相同：

The result should be the same as:

SELECT s.period, s.year, s.amount 
FROM salaries s
  JOIN periods p ON s.period = p.period AND s.year = p.year

推荐答案

您可以使用多个 IN 条件的列：

You can use more than one column for an IN condition:

SELECT s.period, s.year, s.amount 
FROM salaries s
where (s.year, s.period) in (select year, period from periods)

但是Gordon的不存在解决方案可能更快。

But Gordon's not exists solution is probably faster.

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