create table T( id int,name varchar(4))
insert into T values(1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C');
--选出name有A无B的id号
--方法一:
select * from (select * from T where name='A')as a where ID<>(select id from T where name='B')
还有别的方法来实现吗?
insert into T values(1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C');
--选出name有A无B的id号
--方法一:
select * from (select * from T where name='A')as a where ID<>(select id from T where name='B')
还有别的方法来实现吗?
WHERE NOT EXISTS(SELECT 1 FROM T WHERE ID=T1.ID AND NAME<>T1.NAME AND NAME='B')
and t1.name='a'
INTERSECT
select distinct(id) from T where id in (select id from T where name='A')除了这个,还有别的不
inner join T t2 on (t1.id = t2.id and t1.name <> t2.name)
and t2.name <> 'B'
where t1.name = 'A'
create table T( id int,name varchar(4))
insert into T values(1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C');
select t1.*
from T t1
where t1.name='A' and not exists
(select 1 from T t2
where t2.id=t1.id and t2.name='B')/*
id name
----------- ----
2 A(1 row(s) affected)
*/
where t.id<>a.id
and t.name='A'
FROM t AS a
WHERE NAME = 'a'
AND NOT EXISTS ( SELECT 1
FROM t
WHERE id = a.id
AND NAME = 'b' )
FROM t AS a
WHERE NAME = 'a'
EXCEPT
SELECT * FROM t AS a
WHERE EXISTS ( SELECT 1 FROM t
WHERE id = a.id
AND NAME = 'b' )