同字段多个like 查询交集

[mmkkuoi]

create table a
(
name nvarchar(50),
value nvarchar(50)
)

insert into a values('a','a1'),('a','a1'),('a','a2'),('a','a3'),('b','b1'),('b','b1'),('b','a1')

我想like查询同时有1和3的name
最后查出的结果只有一行a

[發糞塗牆]

select * from a where (name like '%1%' or name like '%3%')

[快溜]

select name from tb where value like '%1%' or value like '%2%' group by name having count(distinct name)=2

[开着拖拉机泡妞]

create table a
(
name nvarchar(50),
value nvarchar(50)
)

insert into a values('a','a1'),('a','a1'),('a','a2'),('a','a3'),('b','b1'),('b','b1'),('b','a1')

select * from a  
where 
exists(select 1 from a s where s.name=a.name and s.value like '%1%')
and exists(select 1 from a s where s.name=a.name and s.value like '%3%')


/*
name                                               value
-------------------------------------------------- --------------------------------------------------
a                                                  a1
a                                                  a1
a                                                  a2
a                                                  a3

(4 行受影响)


*/

[發糞塗牆]

select distinct name from a where (name like '%1%' or name like '%3%')