数据表:部门 日期 数量
011 2005 1234
0222 2005 2345
021 2005 3456
012 2005 4567
011 2005 5678
部门表:编码 父节点编码
01
02
011 01
012 01
022 02
021 02
0222 022 我现在要从数据表中汇总查出根节点 就是 01、02的数据,可以关联部门表进行查询,但是部门表01有2层 02有3层,这样在写SQL语句的时候没办法确定是汇总几层,这样就没办法关联查询,请问,这样的汇总查询应该怎么做呢?
011 2005 1234
0222 2005 2345
021 2005 3456
012 2005 4567
011 2005 5678
部门表:编码 父节点编码
01
02
011 01
012 01
022 02
021 02
0222 022 我现在要从数据表中汇总查出根节点 就是 01、02的数据,可以关联部门表进行查询,但是部门表01有2层 02有3层,这样在写SQL语句的时候没办法确定是汇总几层,这样就没办法关联查询,请问,这样的汇总查询应该怎么做呢?
from 数据表 a,部门表 b
where a.部门 like rtrim(b.编码)+'%'
and b.父节点编码 ='' --b.父节点编码 is null
group by b.编码,a.日期
union all
union all
3层的汇总
union all
4层的汇总
declare @t table(a varchar(5) ,b int ,c int)
insert into @T
select '011', 2005, 1234 union
select '0222', 2005, 2345 union
select '021', 2005, 3456 union
select '012', 2005, 4567 union
select '011', 2005, 5678
select substring(a,1,2), sum(c) from @T
group by substring(a,1,2)
declare @t1 table(code varchar(4),par_code varchar(4))
declare @t2 table(code varchar(4),par_end_code varchar(4))
declare @t3 table(par_end_code varchar(4),sum_qty int)insert into @t
select '011' ,2005 ,1234 union
select '0222' ,2005 , 2345 union
select '021' ,2005 , 3456 union
select '012' ,2005 , 4567 union
select '011' ,2005 , 5678 insert into @t1
select '01', '' union
select '02', '' union
select '011', '01' union
select '012', '01' union
select '022', '02' union
select '021', '02' union
select '0222', '022'declare @s varchar(8)
declare @s1 varchar(8)
declare @s2 varchar(8)
declare cursor1 scroll cursor for select code from @t1 where par_code <> ''
open cursor1
fetch next from cursor1 into @s
while @@fetch_status = 0
begin
select @s1 = par_code from @t1 where code = @s
while(@s1 <> '')
begin
select @s2 = @s1
select @s1 = par_code from @t1 where code = @s1
end
insert into @t2
select @s , @s2
fetch next from cursor1 into @s
endclose cursor1
deallocate cursor1declare @sum_qty int
declare @s3 varchar(8)
declare cursor2 scroll cursor for select par_end_code from @t2 group by par_end_code
open cursor2
fetch next from cursor2 into @s3
while @@fetch_status = 0
begin
select @sum_qty = sum(qty) from @t
where dept in
(
select code from @t2
where par_end_code = @s3
) insert into @t3
select @s3 , @sum_qty
fetch next from cursor2 into @s3
endclose cursor2
deallocate cursor2select * from @t3
create fn_sum(@编码 varchar(30))
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
select @r=sum(数量) from 数据表 where 编码=@编码
if @r is null
set @r=0
select @r=@r+isnull(sum(数量),0)
from 数据表 a,部门表 b
where a.部门=b.编码
and b.父节点编码 =@编码
return @r
end go--查询
select 编码,dbo.fn_sum(编码) as 数量
from 部门表
where 父节点编码 ='' --b.父节点编码 is null
--我没有测试
create fn_sum(@编码 varchar(30))
returns numeric(10,2)
as
begin
declare @r numeric(10,2)
select @r=sum(数量) from 数据表 where 编码=@编码
if @r is null
set @r=0
select @r=@r+sum(dbo.fn_sum(编码))
from 部门表
where b.父节点编码 =@编码/*如果上一句有问题,换成下面一句 呵呵,搞不清楚语法有没问题了
select @r=@r+dbo.fn_sum(编码)
from 部门表
where b.父节点编码 =@编码
*/
return @r
end go--查询
select 编码,dbo.fn_sum(编码) as 数量
from 部门表
where 父节点编码 ='' --b.父节点编码 is null
--我没有测试