SQL语句不会写 求大神帮忙.在线等.....
表A 表B
Id Name Remark Id AId State
1 张山 无 1 1 true
2 王二 无 2 1 false
3 李四 无 3 1 false
4 2 true
5 2 true
6 3 false
7 3 true
我想查询 表B 状态全部为true 的表A数据
结果： ID Name Remark
2 王二 无
[解决办法]
create table 表A(Id int, Name varchar(10), Remark varchar(10))
insert into 表A
select 1 ,'张山' ,'无' union all
select 2 ,'王二' ,'无' union all
select 3 ,'李四' ,'无'
create table 表B(Id int, AId int, State varchar(10))
insert into 表B
select 1 ,1 ,'true' union all
select 2 ,1 ,'false' union all
select 3 ,1 ,'false' union all
select 4 ,2 ,'true' union all
select 5 ,2 ,'true' union all
select 6 ,3 ,'false' union all
select 7 ,3 ,'true'
select *
from 表A
where ID in (select aid from 表B
group by aid
having COUNT(*)=COUNT(case when State = 'true ' then 1 else null end))
/*
IdNameRemark
2王二无
*/
select a.* from
(
SELECT [AId],sum(case when [State]='true' then 0 else 1 end) as total
FROM [Table_B]
group by [AId]
) b inner join [Table_A] a on b.AId=a.Id
where total =0
select * from A t1 where not exists (select * from B t2 where t2.state='false' and t1.id=t2.aid )
if OBJECT_ID('表A') is not null
drop table 表A
create table 表A(Id int, Name varchar(10), Remark varchar(10))
insert into 表A
select 1 ,'张山' ,'无' union all
select 2 ,'王二' ,'无' union all
select 3 ,'李四' ,'无'
if OBJECT_ID('表B') is not null
drop table 表B
create table 表B(Id int, AId int, State varchar(10))
insert into 表B
select 1 ,1 ,'true' union all
select 2 ,1 ,'false' union all
select 3 ,1 ,'false' union all
select 4 ,2 ,'true' union all
select 5 ,2 ,'true' union all
select 6 ,3 ,'false' union all
select 7 ,3 ,'true'
select * from 表A where Id not in
(select AId from 表B where State = 'false')
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-26 17:38:20
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[表A]
if object_id('[表A]') is not null drop table [表A]
go
create table [表A]([Id] int,[Name] varchar(4),[Remark] varchar(2))
insert [表A]
select 1,'张山','无' union all
select 2,'王二','无' union all
select 3,'李四','无'
--> 测试数据：[表B]
if object_id('[表B]') is not null drop table [表B]
go
create table [表B]([Id] int,[AId] int,[State] varchar(5))
insert [表B]
select 1,1,'true' union all
select 2,1,'false' union all
select 3,1,'false' union all
select 4,2,'true' union all
select 5,2,'true' union all
select 6,3,'false' union all
select 7,3,'true'
--------------开始查询--------------------------
select * from [表A] A
WHERE id IN (SELECT aid
FROM [表B] b
WHERE aid NOT IN (SELECT aid FROM [表B] WHERE [state]!='true'))
----------------结果----------------------------
/*
Id Name Remark
----------- ---- ------
2 王二 无
*/
if object_id('tempdb..#a') is not null drop table #a
go
create table #a([Id] int,[Name] varchar(4),[Remark] varchar(2))
insert #a
select 1,'张山','无' union all
select 2,'王二','无' union all
select 3,'李四','无'
--
if object_id('tempdb..#b') is not null drop table #b
go
create table #b([Id] int,[AId] int,[State] varchar(5))
insert #b
select 1,1,'true' union all
select 2,1,'false' union all
select 3,1,'false' union all
select 4,2,'true' union all
select 5,2,'true' union all
select 6,3,'false' union all
select 7,3,'true'
select * from #a
select * from #b
--#########查询 表B 状态全部为true 的表A数据##########
---方法一，使用 not exists 排除为false 的
select * from #a t1 where not exists (select * from #b t2 where t2.state='false' and t1.id=t2.aid )
---方法二
select * from #a where Id not in
(
select AId from #b where State = 'false'
)
---方法三
select *
from #a
where ID in (select aid from #b
group by aid
having COUNT(*)=COUNT(case when State = 'true ' then 1 else null end))
---方法四
select a.* from
(
SELECT AId,sum(case when State='true' then 0 else 1 end) as total
FROM #b
group by AId
) b inner join #a a on b.AId=a.Id
where total =0