SQL2008更新一个表里面同一字段之间相加

软件界的小白 2018-07-20 08:25:11

如图：
同一个表里面以把一样的ptypeid中的prtypeid为0002的price更新为prtpyeid为0101的price+prtpyeid为0004的price只更新ptypeid包含000030的行

...全文

164 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2018-07-21

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([PTypeId] int,[UnitID] int,[PRTypeId] int,[Price] decimal(18,7))

Insert #T

select 100001,0,2,10 union all

select 100001,0,101,52 union all

select 100001,0,102,52 union all

select 100002,0,2,10 union all

select 100002,0,101,60 union all

select 100002,0,102,60 union all

select 300001,0,2,10 union all

select 300001,0,4,0.5 union all

select 300001,0,101,110 union all

select 300001,0,102,110 union all

select 300002,0,2,10 union all

select 300002,0,4,0.2 union all

select 300002,0,101,110 union all

select 300002,0,102,110

Go

--测试数据结束

UPDATE

    c

SET

    Price = a.Price + b.Price

FROM

    #T a,

    #T b,

	#T c

WHERE

    c.PTypeId LIKE '3%'

    AND c.PRTypeId = 2

    AND c.PTypeId = a.PTypeId

    AND c.PTypeId = b.PTypeId

    AND a.PRTypeId = 101

    AND b.PRTypeId = 4;

软件界的小白 2018-07-20

打赏
举报

源数据：
PTypeId UnitID PRTypeId Price
100001 0 2 10
100001 0 101 52
100001 0 102 52
100002 0 2 10
100002 0 101 60
100002 0 102 60
300001 0 2 10
300001 0 4 0.5
300001 0 101 110
300001 0 102 110
300002 0 2 10
300002 0 4 0.2
300002 0 101 110
300002 0 102 110

结果
PTypeId UnitID PRTypeId Price
100001 0 2 10
100001 0 101 52
100001 0 102 52
100002 0 2 10
100002 0 101 60
100002 0 102 60
300001 0 2 110.5
300001 0 4 0.5
300001 0 101 110
300001 0 102 110
300002 0 2 110.2
300002 0 4 0.2
300002 0 101 110
300002 0 102 110