求一数据库查询语句，只有一张表,该如何处理

求一数据库查询语句，只有一张表
求一数据库查询语句，只有两张表

SQL code

TableAID name1  刘2  王3  李4  吴5  陈TableBID No Comment1  1  AB1  2  AA1  5  AD2  3  AG2  6  AF3  5  DD4  1  MM4  5  KL

我想取出No只有1，5的数据：也就是ID=4的 4 吴 那条数据。
PS：ID=1的那个因为多了No=2所以不要；ID=2的那个就没有1，5，且多了3，6，所以也不要；ID=3的那个少了1，所以不要；
ID=4，刚好只有1，5，取出；ID=5，在TableB里没数据，所以不要。

[解决办法]

SQL code

select a.*from table ajoin(select id from tableb where no in(1,5) having count(distinct no)=2) bon a.id=b.id
[解决办法]
SQL code
create table TableA(ID int,name varchar(10))insert into tablea values(1 , '刘')insert into tablea values(2 , '王')insert into tablea values(3 , '李')insert into tablea values(4 , '吴')insert into tablea values(5 , '陈')create table TableB(ID int,No int,Comment varchar(10))insert into tableb values(1 , 1 , 'AB')insert into tableb values(1 , 2 , 'AA')insert into tableb values(1 , 5 , 'AD')insert into tableb values(2 , 3 , 'AG')insert into tableb values(2 , 6 , 'AF')insert into tableb values(3 , 5 , 'DD')insert into tableb values(4 , 1 , 'MM')insert into tableb values(4 , 5 , 'KL')goselect a.* from tablea a ,(  select distinct id from tableb where no = 1  union all  select distinct id from tableb where no = 5) b where a.id = b.id and b.id not in (select id from tableb where no not in (1,5))group by a.id , a.name having count(1) = 2drop table tablea , tableb/*ID          name       ----------- ---------- 4           吴（所影响的行数为 1 行）*/
[解决办法]
go
create table #a(
ID int,
name varchar(2)
)
go
insert #a
select 1,'刘' union all
select 2,'王' union all
select 3,'李' union all
select 4,'吴' union all
select 5,'陈'

go
create table #b(
ID int,
Num int,
Comment varchar(2)
)
go  
insert #b
select 1,1,'AB' union all
select 1,2,'AA' union all
select 1,5,'AD' union all
select 2,3,'AG' union all
select 2,6,'AF' union all
select 3,5,'DD' union all
select 4,1,'MM' union all
select 4,5,'KL'


select * from #a where ID in
(select ID from
(select distinct ID from #b where Num = 1
 union all
 select distinct ID from #b where Num = 5)t
where ID not in (select ID from #b where Num not in (1,5))
group by ID having count(1)=2) 

 

查看更多 下一篇