--try
select * from tb
where not exists(select 1 from tb t where name=tb.name and item not in ('t','s'))
select * from tb
where not exists(select 1 from tb t where name=tb.name and item not in ('t','s'))
调试欢乐多
insert tb select 1, 'a', 't'
union all select 2, 'a', 's'
union all select 3, 'a', 'w'
union all select 4, 'b', 't'
union all select 5, 'b', 's'
union all select 6, 'c', 't'
union all select 7, 'c', 't'--try
select * from tb
where not exists(select 1 from tb t where name=tb.name and item not in ('t','s'))drop table tb /*
id name item
----------- -------------------------------- --------------------------------
4 b t
5 b s
6 c t
7 c t(4 row(s) affected)
*/
select * from tb where name in ('b' , 'c') and item in ('t' , 's')
create table tb(id int, name varchar(32), item varchar(32))
insert tb select 1, 'a', 't'
union all select 2, 'a', 's'
union all select 3, 'a', 'w'
union all select 4, 'b', 't'
union all select 5, 'b', 's'
union all select 6, 'c', 't'
union all select 7, 'c', 't'--这样?
select * from tb where name in ('b' , 'c') and item in ('t' , 's')--还是这样?select * from tb where name in ('b' , 'c')drop table tb/*
id name item
----------- -------------------------------- --------------------------------
4 b t
5 b s
6 c t
7 c t(所影响的行数为 4 行)id name item
----------- -------------------------------- --------------------------------
4 b t
5 b s
6 c t
7 c t(所影响的行数为 4 行)
*/
insert tb select 1, 'a', 't'
union all select 2, 'a', 's'
union all select 3, 'a', 'w'
union all select 4, 'b', 't'
union all select 5, 'b', 's'
union all select 6, 'c', 't'
union all select 7, 'c', 't'
select * from tb where name not in (select distinct name from tb where item not in ('t','s'))
id name item
----------- -------------------------------- --------------------------------
4 b t
5 b s
6 c t
7 c t(4 行受影响)