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

sybase2000 2011-11-11 08:45:10
一个账务系统中的凭证表中的数据是这样的：
日期 编号 顺序号 摘要 代码 部门 借金额 贷金额 附件张数
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
...全文
181 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
-晴天 2011-11-30
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 sybase2000 的回复:]

又重新编辑了一次，明明编辑的时候是对上的，发表后又对不上了，估计论坛处理有问题，非“所见即所得”
[/Quote]

编辑好,剪切,在编辑工具栏上有个源代码按钮,选SQL,然后粘贴到那个里面,它就能保持格式了.
中国风 2011-11-30
  • 打赏
  • 举报
 回复
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.日期)
中国风 2011-11-30
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 sybase2000 的回复:]

非常感谢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……
[/Quote]



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

*/
sybase2000 2011-11-30
  • 打赏
  • 举报
 回复
谢谢了，还想请教一个问题，我想把合并的结果保存到一个表中，应该怎么写？（新手，见笑了）
sybase2000 2011-11-29
  • 打赏
  • 举报
 回复
非常感谢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

上一条就不会显示了，怎么办？



勿勿 2011-11-22
  • 打赏
  • 举报
 回复
膜拜
[Quote=引用 15 楼 roy_88 的回复:]
2011-11-2 102 2 王五 100301 5 400 0 5 101、102--这里多了101




SQL code
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[顺……
[/Quote]
中国风 2011-11-21
  • 打赏
  • 举报
 回复
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

*/
sybase2000 2011-11-21
  • 打赏
  • 举报
 回复
多谢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
中国风 2011-11-15
  • 打赏
  • 举报
 回复
看看是不是这样？
--> --> (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

*/
中国风 2011-11-15
  • 打赏
  • 举报
 回复
2011-11-2 102 2 王五 100301 5 400 0 5

附件张数=应该是3吧？部门不同没有合并

中国风 2011-11-11
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 sybase2000 的回复:]

我编辑的时候格式是对齐的，一发表就乱了，不知怎么回事

还有个附件张数没有加？
[/Quote]
欄位都沒對上,你自己調整一下,沒有值的欄位用null
sybase2000 2011-11-11
  • 打赏
  • 举报
 回复
我编辑的时候格式是对齐的，一发表就乱了，不知怎么回事

还有个附件张数没有加？
中国风 2011-11-11
  • 打赏
  • 举报
 回复
格式真亂，能把格式對齊麼
中国风 2011-11-11
  • 打赏
  • 举报
 回复 
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,null



Go

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			

*/
sybase2000 2011-11-11
  • 打赏
  • 举报
 回复
一张凭证里一般最起码有二条记录，即一借一贷，也有可能是多借多贷，顺序号就表示借贷记录数
sybase2000 2011-11-11
  • 打赏
  • 举报
 回复
我是以一个编号表示一张凭证，一张凭证里有多个借贷，用顺序号来区分，合并就是以凭证为单位，如果凭证上A里面的借方科目和凭证上B里面的的借方科目相同，且凭证上A里面的贷方科目和凭证上B里面的的贷方科目相同，才能进行合并，因此合并时得先判断二张凭证的借方科目和贷方科目是否相同
昵称被占用了 2011-11-11
  • 打赏
  • 举报
 回复
上一贴没说清楚，这一贴对应关系没说清楚
昵称被占用了 2011-11-11
  • 打赏
  • 举报
 回复
呵呵，你的借方贷方在不同的记录，这个人判断还好对应，如果出现同一天借方贷方都出现多条记录怎么对应的
快溜 2011-11-11
  • 打赏
  • 举报
 回复
字段太多，不写了，就是聚合加合并多行数据
sybase2000 2011-11-11
  • 打赏
  • 举报
 回复
又重新编辑了一次，明明编辑的时候是对上的，发表后又对不上了，估计论坛处理有问题，非“所见即所得”
加载更多回复（1）
easypoi 列合并_easypoi实现带有合并单元格数据的导出
以前只做过poi的表格导出，复杂繁琐，这次有个功能需要合并单元格的数据导出，从同事那里取经，发现了一个超级炒鸡好用的导出工具easypoi，啊，天哪，开发这个的真是是最强大脑！！！话不多说了，直接上代码：一：...
表格数据批量合并成一个的详细教程
今天，我就来和大家分享一下我的心得，希望能帮助大家轻松解决这个难题。其实，在开发"一键表格合并助手"的过程中，我遇到了不少有趣的挑战。你只需要选择要合并的表格文件，然后轻轻一点"合并"按钮，剩下的事情就...
easypoi实现带有合并单元格数据的导出
以前只做过poi的表格导出，复杂繁琐，这次有个功能需要合并单元格的数据导出，从同事那里取经，发现了一个超级炒鸡好用的导出工具easypoi，啊，天哪，开发这个的真是是最强大脑！！！话不多说了，直接上代码： 一：...
多个Excel工作表合并成一个的简单步骤
总之,无论你是处理日常工作中的简单表格,还是需要合并大量复杂数据,我的这款工具都能帮你轻松搞定。当然,除了我的这款神器,还有一些其他的方法可以实现Excel表格的合并。这是最基本的方法,适合处理比较简单的表格...
多个表格合并成一张的高效方法
智能合并：能够自动识别相似表头，即使表头不完全一致也能正确合并。自定义功能：可以自定义列、sheet，跳过不需要的说明行，添加数据来源列等。去重功能：支持整行重复数据的去重，保证数据的唯一性。
MS-SQL Server

34,837

社区成员

254,632

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章