MSSQL关于凭证科目递归，求助大神

qq_28607107 2017-12-09 10:50:21

Create table pz



(  



id int not null,  

pid int,  

kemu varchar(100),

d decimal(20,8),

c decimal(20,8),

name varchar(100)

)  

 



insert into pz(id,pid,kemu,d,c,name)   

select 1,null,'1001',200,300,'现金'

union all  

select 2,null,'1002',0,200,'存款' 

union all  

select 3,null,'1003',200,0,'票据'

union all  

select 4,2,'100201',300,0,'农行'

union all  

select 5,4,'10020102',300,0,'盐城'

union all  

select 6,5,'1002010201',500,0,'D卡'

union all  

select   8,1,'100101',1000,600,'A柜'

union all  

select 9,7,'10010102',1500,700,'A柜一层'

union all  

select 7,1,'100102',800,10001,'B柜'

union all  

select 8,1,'100101',700,1200,'A柜'

这是实验数据，要求结果如图

...全文

69 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

qq_28607107 2017-12-09

打赏
举报

引用 2 楼 roy_88 的回复:

或这样用

SELECT  id ,
        pid ,
        kemu ,
        d ,
        c ,
        name2=ISNULL(REPLACE((SELECT name+'-' FROM pz WHERE a.kemu LIKE kemu+'%' AND LEN(kemu)<LEN(a.kemu) ORDER BY kemu FOR XML PATH('')),'-','->'),'')+a.name
FROM    pz AS a
ORDER BY kemu;

包括上面的大神这个还是没合计到下级科目的金额 d c需要需要把下级合计

中国风 2017-12-09

打赏
举报

或这样用

SELECT  id ,
        pid ,
        kemu ,
        d ,
        c ,
        name2=ISNULL(REPLACE((SELECT name+'-' FROM pz WHERE a.kemu LIKE kemu+'%' AND LEN(kemu)<LEN(a.kemu) ORDER BY kemu FOR XML PATH('')),'-','->'),'')+a.name
FROM    pz AS a
ORDER BY kemu;

中国风 2017-12-09

打赏
举报

相同问题发一贴就行了，在另一贴已回复 http://bbs.csdn.net/topics/392295949

中国风 2017-12-09

打赏
举报

e.g.

SELECT  id ,
        pid ,
        kemu ,
        b.d ,
        b.c ,
        name2=ISNULL(REPLACE((SELECT name+'-' FROM pz WHERE a.kemu LIKE kemu+'%' AND LEN(kemu)<LEN(a.kemu) ORDER BY kemu FOR XML PATH('')),'-','->'),'')+a.name
FROM    pz AS a
CROSS APPLY(SELECT SUM(d),SUM(c) FROM pz WHERE kemu LIKE a.kemu+'%') AS b(d,c)
ORDER BY kemu;
/*
id	pid	kemu	d	c	name2
1	NULL	1001	4200.00000000	12801.00000000	现金
8	1	100101	3200.00000000	2500.00000000	现金->A柜
8	1	100101	3200.00000000	2500.00000000	现金->A柜
9	7	10010102	1500.00000000	700.00000000	现金->A柜->A柜->A柜一层
7	1	100102	800.00000000	10001.00000000	现金->B柜
2	NULL	1002	1100.00000000	200.00000000	存款
4	2	100201	1100.00000000	0.00000000	存款->农行
5	4	10020102	800.00000000	0.00000000	存款->农行->盐城
6	5	1002010201	500.00000000	0.00000000	存款->农行->盐城->D卡
3	NULL	1003	200.00000000	0.00000000	票据
*/