一标题,求多方法

一题目,求多方法

SQL code

create table T( id int,name varchar(4))insert into T values(1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C');--选出name有Ａ无Ｂ的id号--方法一：select * from (select * from T where name='A')as a where ID<>(select id from T where name='B')

还有别的方法来实现吗？

[解决办法]
SELECT * FTOM T T1 
WHERE NOT EXISTS(SELECT 1 FROM T WHERE ID=T1.ID AND NAME<>T1.NAME AND NAME='B')
and t1.name='a'
[解决办法]
select t1.* from T as t1
inner join T t2 on (t1.id = t2.id and t1.name <> t2.name) 
and t2.name <> 'B'
where t1.name = 'A'
[解决办法]

SQL code

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 t1where 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)*/
[解决办法]
SQL code
SELECT  *FROM    t AS aWHERE   NAME = 'a'        AND NOT EXISTS ( SELECT 1                         FROM   t                         WHERE  id = a.id                                AND NAME = 'b' ) 

 

查看更多 下一篇