34,590
社区成员
发帖
与我相关
我的任务
分享
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柜'
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柜'
GO
;WITH CTET
AS
(
SELECT *,name2=CAST(name AS VARCHAR(100))FROM pz WHERE pid IS NULL
UNION ALL
SELECT a.*,CAST(b.name2+'->'+a.name AS VARCHAR(100)) FROM pz AS a INNER JOIN CTET AS b ON b.id=a.pid
)
SELECT id ,
pid ,
kemu ,
d ,
c ,
name2 AS name
FROM CTET
ORDER BY CTET.kemu;
/*
id pid kemu d c name
1 NULL 1001 200.00000000 300.00000000 现金
8 1 100101 1000.00000000 600.00000000 现金->A柜
8 1 100101 700.00000000 1200.00000000 现金->A柜
9 7 10010102 1500.00000000 700.00000000 现金->B柜->A柜一层
7 1 100102 800.00000000 10001.00000000 现金->B柜
2 NULL 1002 0.00000000 200.00000000 存款
4 2 100201 300.00000000 0.00000000 存款->农行
5 4 10020102 300.00000000 0.00000000 存款->农行->盐城
6 5 1002010201 500.00000000 0.00000000 存款->农行->盐城->D卡
3 NULL 1003 200.00000000 0.00000000 票据
*/
;WITH CTET
AS
(
SELECT *,name2=CAST(name AS VARCHAR(100))FROM pz WHERE pid IS NULL
UNION ALL
SELECT a.*,CAST(b.name2+'->'+a.name AS VARCHAR(100)) FROM pz AS a INNER JOIN CTET AS b ON b.id=a.pid
)
SELECT id ,
pid ,
kemu ,
b.d ,
b.c ,
name2 AS name
FROM CTET AS a
CROSS APPLY(SELECT SUM(d),SUM(c) FROM CTET WHERE kemu LIKE a.kemu+'%') AS b(d,c)
ORDER BY a.kemu;
/*
id pid kemu d c name
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 现金->B柜->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 票据
*/