小计合计递归

[kalphon]

RC RN num
440203 广东韶关武江区 2
440201 广东韶关浈江区 1
4402 广东韶关市 3
440132 广东广州萝岗区 1
440131 广东广州南沙区 2
4401 广东广州市 4
44 广东省 7 (3+4)

其中4402没有记录，所以4402显示的是2(440201)+1(440203),4401有1条记录，所以4401显示的是1+1(440132)+2(440131)

请问怎么实现？

[AcHerat]

没看明白！

[jiemo587]

你想说明什么呢？

[--小F--]

4401有1条记录?哪里来的？

[kalphon]

create table #t(a int,b varchar(10))
insert into #t values(1,'440203')
insert into #t values(2,'440203')
insert into #t values(3,'440201')
insert into #t values(4,'440132')
insert into #t values(5,'440131')
insert into #t values(6,'440131')
insert into #t values(7,'4401')

我想做逐级小计，小计数=下级记录总数+自身记录总数
比如4401，下级有3条记录，本身有1条记录，所以4401显示4

[--小F--]

递归的交给晴天大大

[kalphon]

期望的结果是：

code num
440203 2
440201 1
4402 3
440132 1
440131 2
4401 4
44 7

我折腾了很久了，求高人指导

[-晴天]

create table #t(a int,b varchar(10))  
insert into #t values(1,'440203')  
insert into #t values(2,'440203')  
insert into #t values(3,'440201')  
insert into #t values(4,'440132')  
insert into #t values(5,'440131')  
insert into #t values(6,'440131')  
insert into #t values(7,'4401')

select left(b,2),count(*) from #t group by left(b,2)
select left(b,4),count(*) from #t group by left(b,4)
go
/*
(1 行受影响)
     
---- -----------
44   7

(1 行受影响)

         
-------- -----------
4401     4
4402     3

(2 行受影响)

*/

[kalphon]

[Quote=引用 6 楼 kalphon 的回复:]

期望的结果是：

code num
440203 2
440201 1
4402 3
440132 1
440131 2
4401 1
4401 4
44 7

我折腾了很久了，求高人指导
[/Quote]

期望结果调整下

[-晴天]

create table #t(a int,b varchar(10))  
insert into #t values(1,'440203')  
insert into #t values(2,'440203')  
insert into #t values(3,'440201')  
insert into #t values(4,'440132')  
insert into #t values(5,'440131')  
insert into #t values(6,'440131')  
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b

go
drop table #t
/*
b          ct
---------- -----------
44         7
4401       4
4402       3
44013      3
44020      3
440131     2
440132     1
440201     1
440203     2

(9 行受影响)

*/

[-晴天]

[Quote=引用 8 楼 kalphon 的回复:]
引用 6 楼 kalphon 的回复:

期望的结果是：

code num
440203 2
440201 1
4402 3
440132 1
440131 2
4401 1
4401 4 -------------------------这儿貌似是440
44 7

我折腾了很久了，求高人指导


期望结果调整下
[/Quote]

create table #t(a int,b varchar(10))  
insert into #t values(1,'440203')  
insert into #t values(2,'440203')  
insert into #t values(3,'440201')  
insert into #t values(4,'440132')  
insert into #t values(5,'440131')  
insert into #t values(6,'440131')  
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,3),count(*) from #t group by left(b,3)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b order by b desc

go
drop table #t
/*
b          ct
---------- -----------
440203     2
440201     1
4402       3
440132     1
440131     2
4401       4
440        7
44         7

(8 行受影响)

*/

[kalphon]

感谢晴天大大

你提到的 “-------------------------这儿貌似是440“，其实是4401subtotal,为了避免混淆，客户也调整了输入的需求

b ct
440203 2
440201 1
4402subtotal 3
440132 1
440131 2
4401 1
4401subtotal 4 -------------------------这儿貌似是440
44subtotal 7

之前我找过逐级小计的例子，但重装电脑之后就丢失了，还请你指导下

[kalphon]

[Quote=引用 11 楼 kalphon 的回复:]

感谢晴天大大

你提到的 “-------------------------这儿貌似是440“，其实是4401subtotal,为了避免混淆，客户也调整了输出的需求

b ct
440203 2
440201 1
4402subtotal 3
440132 1
440131 ……
[/Quote]


应该是客户也调整了输出的需求，到错字

[yuanwza]

create table #t(a int,b varchar(10))  
insert into #t values(1,'440203')  
insert into #t values(2,'440203')  
insert into #t values(3,'440201')  
insert into #t values(4,'440132')  
insert into #t values(5,'440131')  
insert into #t values(6,'440131')  
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,3),count(*) from #t group by left(b,3)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b order by b desc

go
drop table #t
/*
b          ct
---------- -----------
440203     2
440201     1
4402       3
440132     1
440131     2
4401       4
440        7
44         7

(8 行受影响)

*/

[yuanwza]

create table #t(a int,b varchar(10))  
insert into #t values(1,'440203')  
insert into #t values(2,'440203')  
insert into #t values(3,'440201')  
insert into #t values(4,'440132')  
insert into #t values(5,'440131')  
insert into #t values(6,'440131')  
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b

go
drop table #t
/*
b          ct
---------- -----------
44         7
4401       4
4402       3
44013      3
44020      3
440131     2
440132     1
440201     1
440203     2

(9 行受影响)

*/