求一数据库查询语句，只有一张表
求一数据库查询语句，只有两张表
- 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)