I have a table like this (this is simplified)
| ID | FOO | BAR |
| ------------ | ------------ | ------------ |
| 1 | 100 | 200 |
| 1 | 101 | 202 |
| 1 | 102 | 205 |
| 2 | 100 | 200 |
| 2 | 101 | 222 |
| 2 | 102 | 203 |
| 3 | 100 | 201 |
| 3 | 101 | 202 |
| 3 | 102 | 204 |
| 4 | 100 | 201 |
| 4 | 101 | 202 |
| 4 | 102 | 205 |
If i query FOO = 100 and BAR = 200 the IDs returned are 1 and 2 that's all fine.
I would like to be able to do is FOO = 100 and BAR = 200 and then FOO=101 and BAR = 202 so i get returned only ID = 1
and if i did FOO = 100 and BAR = 201 and FOO = 101 AND BAR = 202 and FOO = 102 and BAR = 205 i would only be returned ID 4
if i did FOO = 100 and BAR = 201 the i would see the IDs 3 and 4
I think i should be able to do this with a recursive _CTE similar to Recurisve query in SQL Server but i cant quite get me head around how to structure it.
The levels that FOO can go too are not limited to 3 its just been simplified for this example
2条答案
按热度按时间1rhkuytd1#
If I correctly understand, you need common
ID
's for every condition.If so, and if
FOO, BAR
are unique for perID
, then you can do this:Note that here:
count(*) = 3
, number3
is count of conditions inwhere
clause, for example if you have just one condition(FOO = 100 and BAR = 201)
, then query is:tkqqtvp12#
No need for recursive CTE. FOR FOO = 100 and BAR = 200 and then FOO=101 and BAR = 202, you can do this.
For FOO = 100 and BAR = 201 and FOO = 101 AND BAR = 202 and FOO = 102 and BAR = 205, you can do this: