最简单的方法是自动加入一个表来创建“AND-filter”？

I have the following (very simple) table:

我有以下(非常简单)表:

| A | Z |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 5 |

Now I want to filter the values in this table by Z and find all As that match my filter. The Z-Filter should behave like an AND-filter. Here are some examples with matching queries:

现在我想通过Z过滤此表中的值,并找到与我的过滤器匹配的所有As。 Z-Filter应该像AND滤波器一样运行。以下是匹配查询的一些示例:

-- filter z= 1
-- expect a = 1,2
select a from data where z = 1;

-- filter z= 5
-- expect a = 2
select a from data where z = 5;

-- filter z= -
-- expect a = 1,2
select a from data;

Now here comes the problem: I want to filter by z=1 AND z=5

现在出现了问题:我希望按z = 1和z = 5进行过滤

-- filter z: 1,5
-- expect a = 2

Using "in" will not give the result I want. It's like saying z=1 OR z=5.

使用“in”不会给出我想要的结果。这就像说z = 1或z = 5。

select a from data where z in (1,5);

I want an AND-join to the z-filter. I know it can be done like this:

我想要一个AND-join到z-filter。我知道可以这样做:

select d1.a from data d1
  left join data d2 on (d1.a = d2.a)
  where d1.z = 1
  and d2.z = 5;

The thing is that the z-filter-list can grow dynamically and for every z-value I have to add another join. So my question is: Is there an easier way to do this?

问题是z-filter-list可以动态增长,对于每个z值,我必须添加另一个连接。所以我的问题是:有更简单的方法吗?

SQLFiddle with the above examples: http://sqlfiddle.com/#!2/03d07/4

SQLFiddle与上面的例子:http://sqlfiddle.com/#!2 / 03d07 / 4

1 个解决方案

#1

You can use GROUP BY/HAVING:

您可以使用GROUP BY / HAVING:

SELECT  a
FROM    data 
WHERE   z IN (1,5)
GROUP BY a
HAVING COUNT(DISTINCT z) = 2; -- NUMBER OF ITEMS IN THE IN CLAUSE

So although you are saying z can be 1 or 5, your HAVING limits this to only values of a that have both.

因此,尽管您说z可以是1或5,但是您的HAVING将此限制为仅具有两者的值。

Example on SQL Fiddle

关于SQL小提琴的例子

#1