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
3
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将此限制为仅具有两者的值。
关于SQL小提琴的例子
#1
3
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将此限制为仅具有两者的值。
关于SQL小提琴的例子