34,590
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
create table a
(
jtbh varchar(20),
xm varchar(20),
sfzhm int,
zw varchar(10),
hnzdrs int
)
insert into a
select '15001','线一','352101','工人',03 union all
select '15002','张二','352105','工人',01 union all
select '15003','王二','352106','工人',04 union all
select '15004','王六','352106','工人',05
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
create table b
(
brbh varchar(20),
xm varchar(20),
sfzhm int,
zw varchar(10)
)
insert into b
select '1500101','张一',352101,'工人' union all
select '1500102','张二',352102,'农民' union all
select '1500103','张三',352103,'农民' union all
select '1500201','张三',352104,'农民' union all
select '1500301','王三',352105,'农民' union all
select '1500302','王二',352106,'农民' union all
select '1500303','王六',352107,'农民' union all
select '1500304','王七',352108,'农民'
update a set hnzdrs =
(select count(1) from b where left(brbh,5)=a.jtbh)
select * from a
/**
jtbh xm sfzhm zw hnzdrs
-------------------- -------------------- ----------- ---------- -----------
15001 线一 352101 工人 3
15002 张二 352105 工人 1
15003 王二 352106 工人 4
15004 王六 352106 工人 0
(所影响的行数为 4 行)
**/
update a
set
hnzdrs =right(100+isnull(b.num,0),2)
from
a,(select grbh,count(1) as num from B group by grbh) b
where
b.grbh like a.jtbh+'%'
update a set hnzdrs=( select count(*) from b where left (grbh,5)=a.jtbh)
--try
update ta set hnzdrs=(select count(*) from tb where left(grbh,5)=ta.jtbh)