a 表
yyid zt
1 0
2 0
b 表
id yyid ddsl phsl
1 1 10 10
2 1 14 15
3 2 5 4
4 2 6 5
a表的yyid和b表对应yyid对应,当a表对应的b表中所有明细的phsl>ddsl时,更新a表的zt=1否则为0上述所示结果为
yyid zt
1 1
2 0
yyid zt
1 0
2 0
b 表
id yyid ddsl phsl
1 1 10 10
2 1 14 15
3 2 5 4
4 2 6 5
a表的yyid和b表对应yyid对应,当a表对应的b表中所有明细的phsl>ddsl时,更新a表的zt=1否则为0上述所示结果为
yyid zt
1 1
2 0
update a.zt = 1 from a,b
where a.yyid = b.yyid
and b.phsl>b.ddsl
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-25 14:30:55
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([yyid] int,[zt] int)
insert [a]
select 1,0 union all
select 2,0
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[yyid] int,[ddsl] int,[phsl] int)
insert [b]
select 1,1,10,10 union all
select 2,1,14,15 union all
select 3,2,5,4 union all
select 4,2,6,5
--------------开始查询--------------------------
update
a
set
a.zt = 1
from
a,b
where
a.yyid = b.yyid
and
b.phsl>=b.ddslselect * from a
----------------结果----------------------------
/* ----------- -----------
1 1
2 0(2 行受影响)
*/
update a set a.zt = 1 from a,b
where a.yyid = b.yyid
and b.phsl>b.ddsl
b 表
id yyid ddsl phsl
1 1 10 10
2 1 14 15
3 2 5 4
4 2 6 5 红色的那条都不符合
怎么来的结果。。楼主。。
create table a(yyid int, zt int)
insert into a(yyid, zt)
select
1,0 union all select
2,0;create table b(id int, yyid int, ddsl int, phsl int);
insert into b(id, yyid, ddsl, phsl)
select
1,1,10,10 union all select
2,1,14,15 union all select
3,2,5,4 union all select
4,2,6,5 union all select
4,2,6,6----按楼主的意思:应该是 >= 的条件吧?
update a set zt=1
where yyid in (
select yyid
from b
group by yyid
having count(id)=sum(case when phsl>=ddsl then 1 else 0 end) )
DECLARE @T TABLE(yyid INT,ddsl INT,phsl INT)
INSERT INTO @T
SELECT 1,10,10 UNION ALL
SELECT 1,14,15 UNION ALL
SELECT 2,5,4 UNION ALL
SELECT 2,6,5SELECT * FROM
(
SELECT yyid,CASE WHEN COUNT(1)=
SUM(CASE WHEN ISNULL(phsl,0)>=ISNULL(ddsl,0) THEN 1 ELSE 0 END) THEN 1 ELSE 0 END flag
FROM @T GROUP BY yyid
)b/*
yyid flag
----------- -----------
1 1
2 0(2 行受影响)
*/
update a
set a.zt=1
from a left outer join b on a.yyid=b.yyid
where b.phsl>b.ddsl
其实如果有必要,做个触发器自动更新a表的数据更好