现在有两个表a,b
表a是这样的
区分 金额
----------------------------------------------
1 100
1 200
2 100
3 100表b
金额
------------------------
50想要的结果是,如果有区分为1的记录的话,就把50更新到区分为1的复数条里的最大的,如果没有区分为1的记录就更新区分为2的记录,如果没有为2的就更新3的结果区分 金额
----------------------------------------------
1 100
1 250
2 100
3 100
表a是这样的
区分 金额
----------------------------------------------
1 100
1 200
2 100
3 100表b
金额
------------------------
50想要的结果是,如果有区分为1的记录的话,就把50更新到区分为1的复数条里的最大的,如果没有区分为1的记录就更新区分为2的记录,如果没有为2的就更新3的结果区分 金额
----------------------------------------------
1 100
1 250
2 100
3 100
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-30 11:10:04
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([区分] int,[金额] int)
insert [a]
select 1,100 union all
select 1,200 union all
select 2,100 union all
select 3,100
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([金额] int)
insert [b]
select 50
--------------开始查询--------------------------
PRINT 'update 前'
SELECT * FROM a
go
UPDATE A
SET A.[金额]=A.[金额]+(SELECT [金额] FROM b)
FROM A INNER JOIN (
select MIN([区分])[区分],MAX([金额]) MAX金额
from [a])B ON A.[区分]=b.区分 AND a.[金额]=b.MAX金额
PRINT 'update 后'
SELECT * FROM a
----------------结果----------------------------
/*
update 前
区分 金额
----------- -----------
1 100
1 200
2 100
3 100
update 后
区分 金额
----------- -----------
1 100
1 250
2 100
3 100
*/
insert #A
select 1,100 union all select 1 ,200 union all select 2,100 union all select 3,100create table #B(amt int)
insert #B values(50)
update #A set amt=#A.amt+b.amt from #B b,(select ID,MAX(amt) amt from #A a where not exists (select 1 from #A where id<a.id) group by id) c
where #A.id=c.id and #A.amt=c.amt
select * from #a
from [a] 有点凑巧了,最小分区对应的最大金额
go
create table [a]([区分] int,[金额] int)
insert [a]
select 1,100 union all
select 1,200 union all
select 1,300 union all
select 3,100
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([金额] int)
insert [b]
select 50
--------------开始查询--------------------------
SELECT * FROM a
UPDATE a
SET a.金额=a.金额+(SELECT [金额] FROM b)
FROM a
WHERE EXISTS(SELECT 1 FROM (SELECT 区分,MAX(金额)金额 FROM a WHERE 区分 =(SELECT MIN(区分)区分 FROM a) GROUP BY 区分)b WHERE a.区分=b.区分 AND a.金额=b.金额)SELECT * FROM a/*
区分 金额
----------- -----------
1 100
1 200
1 300
3 100(4 row(s) affected)(1 row(s) affected)区分 金额
----------- -----------
1 100
1 200
1 350
3 100
*/