这个超级复杂的合并功能应该怎么实现

Question

一个账务系统中的凭证表中的数据是这样的：
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数
2011-11-1 101 1 张三 100301 4 500 0 2
2011-11-1 101 2 200001 0 500 2
2011-11-2 102 1 李四 100301 4 300 0 3
2011-11-2 102 2 王五 100301 5 400 0 3
2011-11-2 102 3 200001 0 700 3
2011-11-4 103 1 陈六 100301 4 700 0 1
2011-11-4 103 2 200002 700 1主键为编号+顺序号，一个编号代表一张凭证，顺序号代码一张凭证里的分录，要求不同凭证号如果借方代码和贷方代码相同且借方向相同，就进行合并，合并后生成的凭证中的日期和编号取合并数据中的最大者，摘要按字符串连接处理，附件张数进行累加，比如编号为101的凭证第一条代码为100031,金额为借方500，第二张代码为200001,金额为贷方500,编号为102的凭证代码为100031,金额在借方，200001金额在贷方，因此二张凭证科目相同且借贷方向一致，可以合并；编号为103的凭证虽然代码为100031金额在借方，但贷方科目为200002,与101和102不同相，因此不能合并，合并结果如下：
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数
2011-11-2 102 1 张三、李四 100301 4 800 0 5
2011-11-2 102 2 王五 100301 5 400 0 5
2011-11-2 102 3 200001 0 1200 5
2011-11-4 103 1 陈六 100301 4 700 0 1
2011-11-4 103 2 200002 700 1

Answer 1

我是以一个编号表示一张凭证，一张凭证里有多个借贷，用顺序号来区分，合并就是以凭证为单位，如果凭证上A里面的借方科目和凭证上B里面的的借方科目相同，且凭证上A里面的贷方科目和凭证上B里面的的贷方科目相同，才能进行合并，因此合并时得先判断二张凭证的借方科目和贷方科目是否相同

Answer 2

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([编号] int,[顺序号] int,[代码] int,[部门] int,[借金额] int,[贷金额] int,[摘要] nvarchar(200))
Insert #T
select 101,1,100301,4,500,0,N'张三' union all
select 101,2,200001,0,500,null,null union all
select 102,1,100301,4,300,0,N'王五' union all
select 102,2,100301,5,400,0,N'陈六' union all
select 102,3,200001,0,700,null,null union all
select 103,1,100401,2,600,0,N'周七' union all
select 103,2,200801,0,600,null,nullGo
Select [编号]=MAX([编号]),[顺序号]=MAX([顺序号]),[代码],[部门],SUM([借金额]) AS [借金额],ISNULL(RTRIM(SUM([贷金额])),'') AS [贷金额],
ISNULL(STUFF((SELECT ISNULL('、'+[摘要],'') FROM #T WHERE [代码]=a.[代码] AND [部门]=a.[部门] FOR XML PATH('')),1,1,''),'') AS [摘要]
from #T AS a
GROUP BY [代码],[部门]
ORDER BY 1,2/*
编号 顺序号 代码 部门 借金额 贷金额 摘要
102 1 100301 4 800 0 张三、王五
102 2 100301 5 400 0 陈六
102 3 200001 0 1200
103 1 100401 2 600 0 周七
103 2 200801 0 600
*/

Answer 3

欄位都沒對上,你自己調整一下,沒有值的欄位用null

Answer 4

一个账务系统中的凭证表中的数据是这样的：日期        编号   顺序号   摘要     代码     部门   借金额   贷金额   附件张数
2011-11-1  101     1     张三   100301    4      500      0       2
2011-11-1  101     2     NULL  200001    NULL     0    500       2
2011-11-2  102     1     李四   100301    4      300      0       3
2011-11-2  102     2     王五   100301    5      400      0       3
2011-11-2  102     3     NULL  200001    NULL     0    700       3
2011-11-4  103     1     陈六   100301    4      700      0       1
2011-11-4  103     2     NULL  200002    NULL     0    700       1
合并结果如下：
日期         编号 顺序号     摘要          代码    部门  借金额  贷金额  附件张数
2011-11-2  102    1   张三、李四       100301   4     800      0     5
2011-11-2  102    2   王五             100301   5     400      0     5
2011-11-2  102    3   NULL           200001   NULL    0   1200     5
2011-11-4  103    1   陈六             100301   4     700      0     1
2011-11-4  103    2   NULL           200002   NULL    0    700     1

Answer 5

看看是不是这样？
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[顺序号] int,[摘要] nvarchar(2),[代码] int,[部门] int,[借金额] int,[贷金额] int,[附件张数] int)
Insert #T
select '2011-11-1',101,1,N'张三',100301,4,500,0,2 union all
select '2011-11-1',101,2,null,200001,null,0,500,2 union all
select '2011-11-2',102,1,N'李四',100301,4,300,0,3 union all
select '2011-11-2',102,2,N'王五',100301,5,400,0,3 union all
select '2011-11-2',102,3,null,200001,null,0,700,3 union all
select '2011-11-4',103,1,N'陈六',100301,4,700,0,1 union all
select '2011-11-4',103,2,null,200002,null,0,700,1
Go
;with c
as
(
Select 
a.*,
checksum(a.[代码],b.[代码]) as checkCode
from #T as a
inner join (select [编号],[代码]=MIN([代码]) from  #T where [贷金额]>0 group by [编号] ) as b on a.[编号]=b.[编号]
where a.借金额>0 
),d
as
(
Select  
b.*,
checksum(a.[代码],b.[代码]) as checkCode
from (select [编号],[代码]=MIN([代码]) from  #T where 借金额>0 group by [编号] ) as a
inner join #T as b on a.[编号]=b.[编号]
where  b.[贷金额]>0
)
select
a.日期,a.编号,a.顺序号, 
摘要=stuff(c.摘要.value('/R[1]','nvarchar(max)'),1,1,''),
a.代码,a.部门,b.借金额,b.贷金额,b.附件张数
from c as a
inner join (select 部门,checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数 from c group by 部门,checkCode ) as b on a.checkCode=b.checkCode and a.部门=b.部门
Cross apply
(select 摘要=(select N'、'+摘要 from c where checkCode=a.checkCode and 部门=a.部门 For XML PATH(''), ROOT('R'), TYPE))c
where not exists(select 1 from c where checkCode=a.checkCode and 部门=a.部门 and 日期>a.日期)
union all
select
a.日期,
a.编号,
a.顺序号, 
a.摘要,
a.代码,
a.部门,
b.借金额,
b.贷金额,
b.附件张数
from d as a
inner join (select checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数 from d group by checkCode ) as b on a.checkCode=b.checkCode 
where not exists(select 1 from d where checkCode=a.checkCode  and 日期>a.日期)
order by 日期,编号,顺序号
/*
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数
2011-11-02 00:00:00.000 102 1 张三、李四 100301 4 800 0 5
2011-11-02 00:00:00.000 102 2 王五 100301 5 400 0 3
2011-11-02 00:00:00.000 102 3 NULL 200001 NULL 0 1200 5
2011-11-04 00:00:00.000 103 1 陈六 100301 4 700 0 1
2011-11-04 00:00:00.000 103 2 NULL 200002 NULL 0 700 1
*/

Answer 6

多谢roy_88,不愧为专家，再想麻烦一下我还想在合并结果中増加一列“合并凭证号”，以表示这笔凭证是由原来的哪几张凭证合并而成（比如新生成的102号凭证是由原来的101和102号凭证合并而成，则表示为101、102；如果没合并，就表示为原来的凭证号（比如103号凭证）,应该怎么写？合并结果如下：
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数 合并凭证号
2011-11-2 102 1 张三、李四 100301 4 800 0 5  101、102
2011-11-2 102 2 王五 100301 5 400 0 5 101、102
2011-11-2 102 3 200001 0 1200 5 101、102
2011-11-4 103 1 陈六 100301 4 700 0 1 103
2011-11-4 103 2 200002 700 1 103

Answer 7

2011-11-2 102 2 王五 100301 5 400 0 5 101、102--这里多了101--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[顺序号] int,[摘要] nvarchar(2),[代码] int,[部门] int,[借金额] int,[贷金额] int,[附件张数] int)
Insert #T
select '2011-11-1',101,1,N'张三',100301,4,500,0,2 union all
select '2011-11-1',101,2,null,200001,null,0,500,2 union all
select '2011-11-2',102,1,N'李四',100301,4,300,0,3 union all
select '2011-11-2',102,2,N'王五',100301,5,400,0,3 union all
select '2011-11-2',102,3,null,200001,null,0,700,3 union all
select '2011-11-4',103,1,N'陈六',100301,4,700,0,1 union all
select '2011-11-4',103,2,null,200002,null,0,700,1
Go
;with c
as
(
Select 
    a.*,
    checksum(a.[代码],b.[代码]) as checkCode
from #T as a
    inner join (select [编号],[代码]=MIN([代码]) from  #T where [贷金额]>0 group by [编号] ) as b on a.[编号]=b.[编号]
where a.借金额>0 
),d
as
(
Select  
    b.*,
    checksum(a.[代码],b.[代码]) as checkCode
from (select [编号],[代码]=MIN([代码]) from  #T where 借金额>0 group by [编号] ) as a
    inner join #T as b on a.[编号]=b.[编号]
where  b.[贷金额]>0
)
select
a.日期,a.编号,a.顺序号, 
摘要=stuff(c.Cols.query('(/a/text())').value('.', 'varchar(max)'), 1, 1, N''),
a.代码,a.部门,b.借金额,b.贷金额,b.附件张数,
合并凭证号=stuff(c.Cols.query('(/b/text())').value('.', 'varchar(max)'), 1, 1, N'')
from c as a
    inner join (select 部门,checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数 from c group by 部门,checkCode ) as b on a.checkCode=b.checkCode and a.部门=b.部门
Cross apply
    (select Cols=(select a=N'、'+摘要,b=N'、'+rtrim([编号]) from c where checkCode=a.checkCode and 部门=a.部门 For XML PATH(''),TYPE))c
where not exists(select 1 from c where checkCode=a.checkCode and 部门=a.部门 and 日期>a.日期)
union all
select
a.日期,
a.编号,
a.顺序号, 
a.摘要,
a.代码,
a.部门,
b.借金额,
b.贷金额,
b.附件张数,
b.合并凭证号
from d as a
    inner join (
    select checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数,
     合并凭证号=stuff((select '、'+RTRIM(编号) from d as c where c.checkCode=d.checkCode for xml path('')),1,1,'')
    from d group by checkCode ) as b on a.checkCode=b.checkCode 
where not exists(select 1 from d where checkCode=a.checkCode  and 日期>a.日期)
order by 日期,编号,顺序号
/*
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数 合并凭证号
2011-11-02 00:00:00.000 102 1 张三、李四 100301 4 800 0 5 101、102
2011-11-02 00:00:00.000 102 2 王五 100301 5 400 0 3 102
2011-11-02 00:00:00.000 102 3 NULL 200001 NULL 0 1200 5 101、102
2011-11-04 00:00:00.000 103 1 陈六 100301 4 700 0 1 103
2011-11-04 00:00:00.000 103 2 NULL 200002 NULL 0 700 1 103
*/

Answer 8

非常感谢roy_88专家的回复，再想麻烦一下，如果我的二笔记录（一张凭证）里的部门都为NULL，执行后只生成一条记录：比如我在表中插入以下二条记录（一张凭证）：2011-11-1,104,1,小陈,100301,NULL,20,0,1
2011-11-1,104,2,NULL,200005,NULL,0,20,1执行以上代码后，104号的凭证只显示一条记录：
2011-11-1,104,2,NULL,200005,NULL,0,20,1，104上一条就不会显示了，怎么办？ 

Answer 9

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[顺序号] int,[摘要] nvarchar(20),[代码] int,[部门] int,[借金额] int,[贷金额] int,[附件张数] int)
Insert #T
select '2011-11-1',101,1,N'张三',100301,4,500,0,2 union all
select '2011-11-1',101,2,null,200001,null,0,500,2 union all
select '2011-11-2',102,1,N'李四',100301,4,300,0,3 union all
select '2011-11-2',102,2,N'王五',100301,5,400,0,3 union all
select '2011-11-2',102,3,null,200001,null,0,700,3 union all
select '2011-11-4',103,1,N'陈六',100301,4,700,0,1 union all
select '2011-11-4',103,2,null,200002,null,0,700,1 UNION ALL
SELECT '2011-11-1',104,1,N'小陈',100301,NULL,20,0,1  UNION ALL
SELECT '2011-11-1',104,2,NULL,200005,NULL,0,20,1
GO
;with c
as
(
Select 
    a.*,
    checksum(a.[代码],b.[代码]) as checkCode
from #T as a
    inner join (select [编号],[代码]=MIN([代码]) from  #T where [贷金额]>0 group by [编号] ) as b on a.[编号]=b.[编号]
where a.借金额>0 
),d
as
(
Select  
    b.*,
    checksum(a.[代码],b.[代码]) as checkCode
from (select [编号],[代码]=MIN([代码]) from  #T where 借金额>0 group by [编号] ) as a
    inner join #T as b on a.[编号]=b.[编号]
where  b.[贷金额]>0
)
select
a.日期,a.编号,a.顺序号, 
摘要=stuff(c.Cols.query('(/a/text())').value('.', 'nvarchar(max)'), 1, 1, N''),
a.代码,a.部门,b.借金额,b.贷金额,b.附件张数,
合并凭证号=stuff(c.Cols.query('(/b/text())').value('.', 'nvarchar(max)'), 1, 1, N'')
from c as a
    inner join (select 部门,checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数 from c group by 部门,checkCode ) as b on a.checkCode=b.checkCode and (a.部门=b.部门 OR ISNULL(a.部门,b.部门) IS NULL)
OUTER apply
    (select Cols=(select a=N'、'+摘要,b=N'、'+rtrim([编号]) from c where checkCode=a.checkCode and (部门=a.部门 OR ISNULL(a.部门,b.部门) IS NULL) For XML PATH(''),TYPE))c
where not exists(select 1 from c where checkCode=a.checkCode and 部门=a.部门 and 日期>a.日期)union all
select
a.日期,
a.编号,
a.顺序号, 
a.摘要,
a.代码,
a.部门,
b.借金额,
b.贷金额,
b.附件张数,
b.合并凭证号
from d as a
    inner join (
    select checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数,
     合并凭证号=stuff((select '、'+RTRIM(编号) from d as c where c.checkCode=d.checkCode for xml path('')),1,1,'')
    from d group by checkCode ) as b on a.checkCode=b.checkCode 
where not exists(select 1 from d where checkCode=a.checkCode  and 日期>a.日期)
order by 日期,编号,顺序号/*
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数 合并凭证号
2011-11-01 00:00:00.000 104 1 小陈 100301 NULL 20 0 1 104
2011-11-01 00:00:00.000 104 2 NULL 200005 NULL 0 20 1 104
2011-11-02 00:00:00.000 102 1 张三、李四 100301 4 800 0 5 101、102
2011-11-02 00:00:00.000 102 2 王五 100301 5 400 0 3 102
2011-11-02 00:00:00.000 102 3 NULL 200001 NULL 0 1200 5 101、102
2011-11-04 00:00:00.000 103 1 陈六 100301 4 700 0 1 103
2011-11-04 00:00:00.000 103 2 NULL 200002 NULL 0 700 1 103
*/

Answer 10

select
    a.日期,a.编号,a.顺序号, 
    摘要=stuff(c.Cols.query('(/a/text())').value('.', 'nvarchar(max)'), 1, 1, N''),
    a.代码,a.部门,b.借金额,b.贷金额,b.附件张数,
    合并凭证号=stuff(c.Cols.query('(/b/text())').value('.', 'nvarchar(max)'), 1, 1, N'')
into T--生成新表
from c as a
    inner join (select 部门,checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数 from c group by 部门,checkCode ) as b on a.checkCode=b.checkCode and (a.部门=b.部门 OR ISNULL(a.部门,b.部门) IS NULL)
OUTER apply
    (select Cols=(select a=N'、'+摘要,b=N'、'+rtrim([编号]) from c where checkCode=a.checkCode and (部门=a.部门 OR ISNULL(a.部门,b.部门) IS NULL) For XML PATH(''),TYPE))c
where not exists(select 1 from c where checkCode=a.checkCode and 部门=a.部门 and 日期>a.日期)或insert into 存在表(日期,编号,顺序号,摘要,代码.....)
select
    a.日期,a.编号,a.顺序号, 
    摘要=stuff(c.Cols.query('(/a/text())').value('.', 'nvarchar(max)'), 1, 1, N''),
    a.代码,a.部门,b.借金额,b.贷金额,b.附件张数,
    合并凭证号=stuff(c.Cols.query('(/b/text())').value('.', 'nvarchar(max)'), 1, 1, N'')from c as a
    inner join (select 部门,checkCode,sum(借金额) as 借金额,sum(贷金额)as 贷金额,sum(附件张数) as 附件张数 from c group by 部门,checkCode ) as b on a.checkCode=b.checkCode and (a.部门=b.部门 OR ISNULL(a.部门,b.部门) IS NULL)
OUTER apply
    (select Cols=(select a=N'、'+摘要,b=N'、'+rtrim([编号]) from c where checkCode=a.checkCode and (部门=a.部门 OR ISNULL(a.部门,b.部门) IS NULL) For XML PATH(''),TYPE))c
where not exists(select 1 from c where checkCode=a.checkCode and 部门=a.部门 and 日期>a.日期)

Answer 11

编辑好,剪切,在编辑工具栏上有个源代码按钮,选SQL,然后粘贴到那个里面,它就能保持格式了.