这种多条件都满足时的查询如何写

这种多条件都满足时的查询怎么写？

有表tab

id,f1,f2
1,a,a
1,b,b
1,c,c
2,d,d

查出满足条件
(f1=a and f2=a) 且 (f1=b and f2=b) 

的所有结果
id,f1,f2
1,a,a
1,b,b
1,c,c

[解决办法]

SQL code

--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]create table [tb]([id] int,[f1] varchar(1),[f2] varchar(1))insert [tb]select 1,'a','a' union allselect 1,'b','b' union allselect 1,'c','c' union allselect 2,'d','d'select * from tbwhere f1=f2 and f1 in('a','b','c')/*id    f1    f2---------------------1    a    a1    b    b1    c    c*/
[解决办法]
SQL code
if object_id('[tab]') is not null drop table [tab]gocreate table [tab]([id] int,[f1] varchar(1),[f2] varchar(1))insert [tab]select 1,'a','a' union allselect 1,'b','b' union allselect 1,'c','c' union allselect 2,'d','d'select * from tabwhere id in(  select id   from tab   where (f1='a' and f2='a' or f1='b' and f2='b')   group by id   having count(distinct f1)=2)/**id          f1   f2----------- ---- ----1           a    a1           b    b1           c    c(3 行受影响)**/