34,575
社区成员
发帖
与我相关
我的任务
分享
update
a
set
amount=case when b.code is null then 0
when b.amount<a.amount then b.amount else a.amount end
from
t1 a ,t2 b
where
a.code=b.code
and
a.dt=b.dt
update #t1 set amount = t2.amount from #t1 t1 , #t2 t2 where t2.code = t1.code and t2.dt = t1.dt and t2.amount < t1.amount
update #t1 set amount = 0 from #t1 t1 where not exists(select 1 from #t2 t2 where t2.code = t1.code and t2.dt = t1.dt)
select * from #t1
/*
id code dt amount
----------- ---------- ------------------------------------------------------ -----------
1 a01 2011-09-15 00:00:00.000 15
2 a01 2011-09-15 00:00:00.000 10
3 a01 2011-09-12 00:00:00.000 0
4 a01 2011-09-14 00:00:00.000 15
5 a01 2011-09-14 00:00:00.000 18
6 a02 2011-09-12 00:00:00.000 0
7 a02 2011-09-14 00:00:00.000 15
8 a02 2011-09-14 00:00:00.000 20
(所影响的行数为 8 行)
*/
create table #t1(id int identity,code varchar(10),dt datetime,amount int)
insert into #t1(code,dt,amount)
select 'a01','2011-09-15',20 union all
select 'a01','2011-09-15',10 union all
select 'a01','2011-09-12',2 union all
select 'a01','2011-09-14',15 union all
select 'a01','2011-09-14',30 union all
select 'a02','2011-09-12',2 union all
select 'a02','2011-09-14',15 union all
select 'a02','2011-09-14',30
create table #t2(id int identity,code varchar(10),dt datetime,amount int)
insert into #t2(code,dt,amount)
select 'a01','2011-09-15',15 union all
select 'a01','2011-09-16',2 union all
select 'a01','2011-09-14',18 union all
select 'a02','2011-09-14',20
go
update t set amount=(case when b.amount>t.amount then t.amount when b.amount is null then 0 else b.amount end)
from #t1 t left join #t2 b on t.code=b.code and t.dt=b.dt-- and b.amount<t.amount
select * from #t1
/*
id code dt amount
----------- ---------- ----------------------- -----------
1 a01 2011-09-15 00:00:00.000 15
2 a01 2011-09-15 00:00:00.000 10
3 a01 2011-09-12 00:00:00.000 0
4 a01 2011-09-14 00:00:00.000 15
5 a01 2011-09-14 00:00:00.000 18
6 a02 2011-09-12 00:00:00.000 0
7 a02 2011-09-14 00:00:00.000 15
8 a02 2011-09-14 00:00:00.000 20
(8 行受影响)
*/
go
drop table #t1,#t2
update #t1 set amount = t2.amount from #t1 t1 , #t2 t2 where t2.code = t1.code and t2.dt = t1.dt and t2.amount > t1.amount
update #t1 set amount = 0 from #t1 t1 where not exists(select 1 from #t2 t2 where t2.code = t1.code and t2.dt = t1.dt)
select * from #t1
/*
id code dt amount
----------- ---------- ------------------------------------------------------ -----------
1 a01 2011-09-15 00:00:00.000 20
2 a01 2011-09-15 00:00:00.000 15
3 a01 2011-09-12 00:00:00.000 0
4 a01 2011-09-14 00:00:00.000 18
5 a01 2011-09-14 00:00:00.000 30
6 a02 2011-09-12 00:00:00.000 0
7 a02 2011-09-14 00:00:00.000 20
8 a02 2011-09-14 00:00:00.000 30
(所影响的行数为 8 行)
*/
create table #t1(id int identity,code varchar(10),dt datetime,amount int)
insert into #t1(code,dt,amount)
select 'a01','2011-09-15',20 union all
select 'a01','2011-09-15',10 union all
select 'a01','2011-09-12',2 union all
select 'a01','2011-09-14',15 union all
select 'a01','2011-09-14',30 union all
select 'a02','2011-09-12',2 union all
select 'a02','2011-09-14',15 union all
select 'a02','2011-09-14',30
create table #t2(id int identity,code varchar(10),dt datetime,amount int)
insert into #t2(code,dt,amount)
select 'a01','2011-09-15',15 union all
select 'a01','2011-09-16',2 union all
select 'a01','2011-09-14',18 union all
select 'a02','2011-09-14',20
go
update t set amount=b.amount from #t1 t inner join #t2 b on t.code=b.code and t.dt=b.dt and b.amount<t.amount
update t set amount=0 from #t1 t where not exists(select 1 from #t2 where code=t.code and dt=t.dt)
select * from #t1
/*
id code dt amount
----------- ---------- ----------------------- -----------
1 a01 2011-09-15 00:00:00.000 15
2 a01 2011-09-15 00:00:00.000 10
3 a01 2011-09-12 00:00:00.000 0
4 a01 2011-09-14 00:00:00.000 15
5 a01 2011-09-14 00:00:00.000 18
6 a02 2011-09-12 00:00:00.000 0
7 a02 2011-09-14 00:00:00.000 15
8 a02 2011-09-14 00:00:00.000 20
(8 行受影响)
*/
go
drop table #t1,#t2
UPDATE T1 SET
amount = CASE WHEN T2.CODE IS NULL THEN 0
WHEN t2.amount < t1.amount THEN t2.amount
ELSE T1.amount
END
FROM #T1 T1 LEFT JOIN #T2 T2
ON T1.CODE = T2.CODE
AND T1.DT = T2.DT