同字段多个like 查询交集
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
[最优解释]
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 行受影响)
*/
