求SQL 语句.在线等.
表A
id name remark
1 张三1 备注1
2 张三2 备注2
3 张三3 备注3
4 张三4 备注4
5 张三5 备注5

表B
Id Aid 状态1 状态2
1 1 true false
2 1 false false
3 1 false false
4 2 true false
5 2 true false
6 2 true true
7 3 false true
8 3 false true
9 4 false false
10 5 true true

我想查询出 表B中状态1为false，状态2为true 注意是全部. (B表数据不固定)
想得到的结果是:
3 张三3 备注3
[解决办法]

引用:

Quote: 引用:

--构造数据
select 1 id ,'张三1' name,'备注1' remark
into #A
union all select 2  ,'张三2','备注2'
union all select 3  ,'张三3','备注3'
union all select 4  ,'张三4','备注4'
union all select 5  ,'张三5','备注5'

 select  1 Id , 1 Aid,'true ' 状态1,'false' 状态2
 into #B
union all select  2 , 1,'false','false'
union all select  3 , 1,'false','false'
union all select  4 , 2,'true ','false'
union all select  5 , 2,'true ','false'
union all select  6 , 2,'true ','true '
union all select  7 , 3,'false','true '
union all select  8 , 3,'false','true '
union all select  9 , 4,'false','false'
union all select 10 , 5,'true ','true '

--查询
select * 
from #A
where id in
(
select aid
from #B
where [状态1]='false' and [状态2]='true'
)

我数据弄错了.不好意思. 如果按照你这么查询 如果存在这样的数据也会被查出来
表B
1 , 5,true ,true
2 , 5,false,true
3 , 5,false,true

--构造数据
select 1 id ,'张三1' name,'备注1' remark
into #A
union all select 2  ,'张三2','备注2'
union all select 3  ,'张三3','备注3'
union all select 4  ,'张三4','备注4'
union all select 5  ,'张三5','备注5'

 select  1 Id , 1 Aid,'true ' 状态1,'false' 状态2
 into #B
union all select  2 , 1,'false','false'
union all select  3 , 1,'false','false'
union all select  4 , 2,'true ','false'
union all select  5 , 2,'true ','false'
union all select  6 , 2,'true ','true '
union all select  7 , 3,'false','true '
union all select  8 , 3,'false','true '
union all select  9 , 4,'false','false'
union all select 10 , 5,'true ','true '

--查询
select * 
from #A
where id in
(
select aid
from
(
select aid,sum(case when [状态1]='false' and [状态2]='true' then 1 end ) ftc
,count(1) rc
from #B
group by aid
) t
where ftc=rc
)

改成这样
[解决办法]
是这样吗：

create table 表A(id  int,name varchar(20), remark varchar(20))  

insert into 表A 
 
 select 1  ,'张三1'  ,'备注1' union all
 select 2  ,'张三2'  ,'备注2' union all
 select 3  ,'张三3'  ,'备注3' union all
 select 4  ,'张三4'  ,'备注4' union all
 select 5  ,'张三5'  ,'备注5'

create table 表B(Id int, Aid int,   状态1 varchar(10),  状态2 varchar(10))

insert into 表B 
 select 1    ,1    ,'true'    ,'false' union all
 select 2    ,1    ,'false'   ,'false' union all
 select 3    ,1    ,'false'   ,'false' union all
 select 4    ,2    ,'true'    ,'false' union all
 select 5    ,2    ,'true'    ,'false'  union all
 select 6    ,2    ,'true'    ,'true'  union all
 select 7    ,3    ,'false'   ,'true'  union all
 select 8    ,3    ,'false'   ,'true' union all
 select 9    ,4    ,'false'   ,'false' union all
 select 10   ,5    ,'true'    ,'true'
go


select  a.*
from 表A a
left join 表B b
       on a.id = b.Aid
group by a.id  ,a.name, a.remark
having COUNT(*) = count(case when b.状态1='false' and b.状态2 = 'true' then 1 else null end )
/*
idnameremark
3张三3备注3
*/

[解决办法]

create table 表A(id  int,[name] varchar(20), remark varchar(20))  
 
insert into 表A
 select 1  ,'张三1'  ,'备注1' union all
 select 2  ,'张三2'  ,'备注2' union all
 select 3  ,'张三3'  ,'备注3' union all
 select 4  ,'张三4'  ,'备注4' union all
 select 5  ,'张三5'  ,'备注5'
 
create table 表B(Id int, Aid int,   状态1 varchar(10),  状态2 varchar(10))
 
insert into 表B 
 select 1    ,1    ,'true'    ,'false' union all
 select 2    ,1    ,'false'   ,'false' union all
 select 3    ,1    ,'false'   ,'false' union all
 select 4    ,2    ,'true'    ,'false' union all
 select 5    ,2    ,'true'    ,'false'  union all
 select 6    ,2    ,'true'    ,'true'  union all
 select 7    ,3    ,'false'   ,'true'  union all
 select 8    ,3    ,'false'   ,'true' union all
 select 9    ,4    ,'false'   ,'false' union all
 select 10   ,5    ,'true'    ,'true'
go
select b.* from 
(
select distinct  Aid from 表B  
where Aid 
not in (select Aid from 表B where 状态1='true') and Aid not in (select Aid from 表B where 状态2='false')
)a left join 表A b on a.Aid=b.id

id name remark
----------- -------------------- --------------------
3 张三3 备注3

(1 行受影响)
[解决办法]

create table 表A(id  int,[name] varchar(20), remark varchar(20))  
  
insert into 表A
 select 1  ,'张三1'  ,'备注1' union all
 select 2  ,'张三2'  ,'备注2' union all
 select 3  ,'张三3'  ,'备注3' union all
 select 4  ,'张三4'  ,'备注4' union all
 select 5  ,'张三5'  ,'备注5'
  
create table 表B(Id int, Aid int,   状态1 varchar(10),  状态2 varchar(10)) 
 
  
insert into 表B 
 select 1    ,1    ,'true'    ,'false' union all
 select 2    ,1    ,'false'   ,'false' union all
 select 3    ,1    ,'false'   ,'false' union all
 select 4    ,2    ,'true'    ,'false' union all
 select 5    ,2    ,'true'    ,'false'  union all
 select 6    ,2    ,'true'    ,'true'  union all
 select 7    ,3    ,'false'   ,'true'  union all
 select 8    ,3    ,'false'   ,'true' union all
 select 9    ,4    ,'false'   ,'false' union all
 select 10   ,5    ,'true'    ,'true'

--------------查询----------------------------
select * from 表A
where id not in 
(select Aid from 表B where 状态1='true'
union
select Aid from 表B where 状态2='false')