ÇóÒ»SQLÓï¾ä

ÓÐA±íÓëB±íÈçÏ£º
A±í
ID BH FLAG
1 a01
2 a02
3 a03

B±í
BH LOCK
a01 1
a01 0
a02 1
a02 1
a03 0
a03 0

¸ù¾ÝB±í£¬¸üÐÂA±íFLAGÊôÐÔ,ÆÚÍû½á¹û£º
ID BH FLAG
1 a01 2
2 a02 1
3 a03 0

---

a01 µÄ2 ÊÇÔõôÀ´µÄ£¿

---

ÒýÓÃ

a01 µÄ2 ÊÇÔõôÀ´µÄ£¿

ͬÎÊ

---

SQL code:
---------------------------------------------
--> Author : js_szy
--> Target : ¸÷λ´ó´ó,С×ä¾ÍÊÇÏëÒªÒ»¶ä»¨
--> Date : 2009-12-02 09:52:22
--> Version: SQL Server 2005
---------------------------------------------

--> ²âÊÔÊý¾Ý: @a
declare @a table (ID int,BH varchar(3),FLAG sql_variant)
insert into @a
select 1,'a01',null union all
select 2,'a02',null union all
select 3,'a03',null

--> ²âÊÔÊý¾Ý: @b
declare @b table (BH varchar(3),LOCK int)
insert into @b
select 'a01',1 union all
select 'a01',0 union all
select 'a02',1 union all
select 'a02',1 union all
select 'a03',0 union all
select 'a03',0

update @a
set flag=b.lock
from @a a,
(select bh,lock=sum(lock) from @b group by bh)b
where a.bh=b.bh

select * from @a

ID BH FLAG
-----------