有两个表如下:
TableA
Field1 State
1 S1
... ...
TableB(情况1)
Field1 Field2
1 a1
1 a1
... ...
TableB(情况2)
Field1 Field2
1 a1
1 a2
... ...
注:TableA.Field1和TableB.Field1关联想实现功能的SQL语句如何写?
对于“情况1”,也就是 TableB中的 Field1=1 且Field2中的值都为 a1 的情况,则将 TabelA中的 State字段 置 为 S3
对于 “情况2”也就是 TableB中的 Field1=1 且Field2中的值不都为 a1 的情况,则 TabelA中的 State字段的值保持不变。
TableA
Field1 State
1 S1
... ...
TableB(情况1)
Field1 Field2
1 a1
1 a1
... ...
TableB(情况2)
Field1 Field2
1 a1
1 a2
... ...
注:TableA.Field1和TableB.Field1关联想实现功能的SQL语句如何写?
对于“情况1”,也就是 TableB中的 Field1=1 且Field2中的值都为 a1 的情况,则将 TabelA中的 State字段 置 为 S3
对于 “情况2”也就是 TableB中的 Field1=1 且Field2中的值不都为 a1 的情况,则 TabelA中的 State字段的值保持不变。
set
State = 'S3'
from
TableA a
where
not exists(select 1 from TableB where Field1=a.Field1 and Field2!='a1')
Update A
Set State='S3'
From TableA A
Where Not Exists(Select 1 From TableB Where Field1=A.Field1 And Field2<>'a1')
insert into @t1 select 1,'S1'
insert into @t1 select 2,'S1'declare @t2 table(Field1 int,Field2 varchar(4))
insert into @t2 select 1,'a1'
insert into @t2 select 1,'a1'
insert into @t2 select 2,'a1'
insert into @t2 select 2,'a2'update a
set
State = 'S3'
from
@t1 a
where
not exists(select 1 from @t2 where Field1=a.Field1 and Field2!='a1')select * from @t1/*
Field1 State
----------- -----
1 S3
2 S1
*/
declare @t1 table(Field1 int,State varchar(4))
insert into @t1 select 1,'S1'
insert into @t1 select 2,'S1'declare @t2 table(Field1 int,Field2 varchar(4))
insert into @t2 select 1,'a1'
insert into @t2 select 1,'a1'
insert into @t2 select 2,'a1'
insert into @t2 select 2,'a2'
Update A
Set State='S3'
From @t1 A
Where (Select Count(Distinct Field2) From @t2 Where Field1=A.Field1)=1select * from @t1/*
Field1 State
----------- -----
1 S3
2 S1
*/
-----------------------------------------------------------------------------------
declare @t1 table(Field1 int,State varchar(4))insert into @t1 select 1,'S1'
insert into @t1 select 2,'S1'
insert into @t1 select 3,'S1'
insert into @t1 select 4,'S1'declare @t2 table(Field1 int,Field2 varchar(4))insert into @t2 select 1,'a1'
insert into @t2 select 1,'a1'insert into @t2 select 2,'a1'
insert into @t2 select 2,'a2'insert into @t2 select 3,null
update a
set
State = 'S3'
from
@t1 a
where
not exists(select 1 from @t2 where Field1=a.Field1 and isnull(Field2,'')!='a1')
and
exists(select 1 from @t2 where Field1=a.Field1 and Field2='a1')select * from @t1/*
Field1 State
----------- -----
1 S3
2 S1
3 S1
4 S1
*/
Set State='S3'
From TableA A
Where Not Exists(Select 1 From TableB Where Field1=A.Field1 And Field2<>'a1')
And Field1 In (Select Distinct Field1 From TableB And Field2='a1'):)
set
State ='S3'
from
@t1 a
where
(select count(1) as num from (select Distinct Field1,Field2 from @t2 ) T where a.Field1=Field1 )=1select * from @t1