----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-18 07:48:19
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise 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]([ID_A] int,[付款人] nvarchar(2),[金额] numeric(3,2),[到帐] nvarchar(2),[合并付款表ID] sql_variant)
insert [A]
select 1,'A',1.00,'Y',null union all
select 2,'B',2.00,'Y',null union all
select 3,'B',3.00,'Y',null union all
select 4,'C',4.00,'N',null
--------------生成数据--------------------------
INSERT INTO 合并付款表(ID ,付款人 ,金额)
select ID_A,[付款人],SUM([金额])[金额]
from [A]
GROUP BY ID_A,[付款人]
UPDATE a
SET a.[合并付款表ID]=b.ID
FROM 合并付款表 b
WHERE a.付款人=b.付款人
----------------结果----------------------------
/*
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-18 07:48:19
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise 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]([ID_A] int,[付款人] nvarchar(2),[金额] numeric(3,2),[到帐] nvarchar(2),[合并付款表ID] sql_variant)
insert [A]
select 1,'A',1.00,'Y',null union all
select 2,'B',2.00,'Y',null union all
select 3,'B',3.00,'Y',null union all
select 4,'C',4.00,'N',null
--------------生成数据--------------------------
INSERT INTO 合并付款表(ID ,付款人 ,金额)
select ID_A,[付款人],SUM([金额])[金额]
from [A]
GROUP BY ID_A,[付款人]
UPDATE a
SET a.[合并付款表ID]=b.ID
FROM 合并付款表 b
WHERE a.付款人=b.付款人
----------------结果----------------------------
/*
*/
INSERT INTO 合并付款表(付款人 ,金额)
select [付款人],SUM([金额])[金额]
from [A]
GROUP BY [付款人]
UPDATE a
SET a.[合并付款表ID]=b.ID
FROM 合并付款表 b
WHERE a.付款人=b.付款人
重名问题应该只出现在对GROUPING以前的记录被覆盖,在本批次下,同样名字的本来就是被合并到到【合并表】
自然也应该被写回【付款表】,所以根据您楼上的代码UPDATE a
SET a.[合并付款表ID]=b.ID
FROM 合并付款表 b
WHERE a.付款人=b.付款人
-- 我加下面俩条件:
AND a.[合并付款表ID] IS NULL
AND a.[到账】='Y'这样,貌似就应该只更新这批次的修改麻烦你看看这样合理么?