求一高效率的更新

jyh070207 2011-09-16 10:49:44
由于需要更新的数据量很大,需要有效率
根据示例数据要求,根据#t2表的code及dt检查,
如果在#t1表没有对应的code及dt,则将#t1表的amount改为0,
如有对应的code及dt而且#t2.amount > #t1.amount 则将#t1.amount 改为#t2.amount,
小于的不改
注意,在#t1同一code及dt可能有多条amount不同的记录存在,
#t2中,code+dt唯一
示例数据如下:
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
select 'a02','2011-09-14',20


更新后#t1的资料应为
select code,dt,amount from #t1
code dt amount
a01 2011-09-15 15--此处将20改为15,因在#t2中,code=a01 and dt = 2011-09-15的amount = 15
a01 2011-09-15 10--这个不改,因10<15
a01 2011-09-12 0--此处将2改为0,因在#t2中,没有对应的code 及dt
a01 2011-09-14 15--这个不改
a01 2011-09-14 18--此处将30改为18
a02 2011-09-12 0--此处将2改为0,因在#t2中,没有对应的code 及dt
a02 2011-09-14 15--这个不改
a02 2011-09-14 20--此处将30改为20


...全文
96 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
jyh070207 2011-09-16
  • 打赏
  • 举报
回复
谢谢各位!
wynlc 2011-09-16
  • 打赏
  • 举报
回复
学习一下
--小F-- 2011-09-16
  • 打赏
  • 举报
回复
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
dawugui 2011-09-16
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 jyh070207 的回复:]
是说反了,
如有对应的code及dt而且#t2.amount <= #t1.amount 则将#t1.amount 改为#t2.amount,
大于的不改
注意,在#t1同一code及dt可能有多条amount不同的记录存在,
[/Quote]
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 行)

*/
-晴天 2011-09-16
  • 打赏
  • 举报
回复
写成一条:
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
dawugui 2011-09-16
  • 打赏
  • 举报
回复
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 行)

*/
苏州牛恋歌 2011-09-16
  • 打赏
  • 举报
回复
2楼正解啊!
-晴天 2011-09-16
  • 打赏
  • 举报
回复
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
jyh070207 2011-09-16
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 nbdba 的回复:]
说反了吧

如有对应的code及dt而且#t2.amount > #t1.amount 则将#t1.amount 改为#t2.amount,
小于的不改

a01 2011-09-15 15--此处将20改为15,因在#t2中,code=a01 and dt = 2011-09-15的amount = 15
a01 2011-09-15 10--这个不改,因10<15

这两段反了……
[/Quote]

是说反了,
如有对应的code及dt而且#t2.amount <= #t1.amount 则将#t1.amount 改为#t2.amount,
大于的不改
注意,在#t1同一code及dt可能有多条amount不同的记录存在,
NBDBA 2011-09-16
  • 打赏
  • 举报
回复
说反了吧

如有对应的code及dt而且#t2.amount > #t1.amount 则将#t1.amount 改为#t2.amount,
小于的不改

a01 2011-09-15 15--此处将20改为15,因在#t2中,code=a01 and dt = 2011-09-15的amount = 15
a01 2011-09-15 10--这个不改,因10<15

这两段反了,不知哪个为准,不过没关系,反了你就自己改回来

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
geniuswjt 2011-09-16
  • 打赏
  • 举报
回复
瞟了一眼应该是case when,等会仔细看下

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧