求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
[解决办法]
--构造数据
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
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')
