SQL Server SQL Recursive match on field with multiple values

sczxawaw  于 2023-05-16  发布在  其他
关注(0)|答案(2)|浏览(142)

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

1rhkuytd

1rhkuytd1#

If I correctly understand, you need common ID 's for every condition.

If so, and if FOO, BAR are unique for per ID , then you can do this:

with the_table(ID, FOO, BAR) as(
select  1 , 100 , 200 union all
select   1 , 101 , 202 union all
select   1 , 102 , 205 union all
select   2 , 100 , 200 union all
select   2 , 101 , 222 union all
select   2 , 102 , 203 union all
select   3 , 100 , 201 union all
select   3 , 101 , 202 union all
select   3 , 102 , 204 union all
select   4 , 100 , 201 union all
select   4 , 101 , 202 union all
select   4 , 102 , 205
)

select id from the_table
where
(FOO = 100 and BAR = 201) or
(FOO = 101 AND BAR = 202) or
(FOO = 102 and BAR = 205)
group by id
having count(*) = 3

Note that here: count(*) = 3 , number 3 is count of conditions in where clause, for example if you have just one condition (FOO = 100 and BAR = 201) , then query is:

select id from the_table
where
(FOO = 100 and BAR = 201)
group by id
having count(*) = 1
tkqqtvp1

tkqqtvp12#

No need for recursive CTE. FOR FOO = 100 and BAR = 200 and then FOO=101 and BAR = 202, you can do this.

with cte
as
(

SELECT ID,FOO,BAR
FROM TAB
WHERE FOO = 100 and BAR = 200
)
SELECT ID
FROM cte
WHERE FOO=101 and BAR = 202

For FOO = 100 and BAR = 201 and FOO = 101 AND BAR = 202 and FOO = 102 and BAR = 205, you can do this:

with cte
    as
    (

    SELECT ID,FOO,BAR
    FROM TAB
    WHERE FOO = 100 and BAR = 201
    )
    SELECT ID 
    FROM
    (SELECT ID,FOO,BAR
    FROM cte
    WHERE FOO=101 and BAR = 202
    ) result(ID,FOO,BAR)
    WHERE FOO=102 and BAR = 205

相关问题