请教SQL多级（3级）数据查询的问题
我查了下，问这个问题的人比较多，但是好像都不是我要的那种
表结构数据如下：
如果有什么好的思路请跟贴告诉我，谢谢。
另，如果还没描述清楚请跟贴说明，我在线等待。 SQL 报表 select
[解决办法]
图片怎么显示不出来呢？
[解决办法]
2690是怎么算的？。。这表有点怪怪的
[解决办法]
go
if OBJECT_ID('tab') is not null
drop table tab
if OBJECT_ID('get_level')is not null
drop function get_level
go
CREATE TABLE tab (accountcod VARCHAR(20),parentcod VARCHAR(20),credit NUMERIC(20,5), debit NUMERIC(20,5))
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('A','',200,120)
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('A01','A',220,350)
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('A0101','A01',150,740)
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('B','',200,620)
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('B01','B',520,280)
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('B0101','B01',500,320)
INSERT INTO tab (accountcod,parentcod,credit,debit) VALUES ('B0102','B01',400,120)
--select * from tab
go
create function get_level(@id varchar(10))
returns @tab table(accountcod varchar(10),[level] int)
as begin
declare @lev int=1
insert into @tab
select accountcod,@lev from tab where accountcod=@id
while (@@rowcount>0)
begin
set @lev=@lev+1
insert into @tab
select t1.accountcod,@lev from tab t1,@tab t2
where t2.accountcod=t1.parentcod and [level]=@lev-1
end
return
end
go
--select * from get_level('A')
with sel as(select b.accountcod,b.level from (select distinct accountcod from tab t where parentcod='') a
cross apply get_level(a.accountcod) b)
,sel2 as(select sel.accountcod,level,credit,debit from tab join sel on tab.accountcod=sel.accountcod )
select accountcod=case when level=1 then accountcod else '' end,
accountcod=case when level=2 then accountcod else ''end,
accountcod=case when level=3 then accountcod else '' end,credit,debit,[SUM]=credit+debit from sel2
/*
accountcod accountcod accountcod credit debit SUM
---------- ---------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------
A 200.00000 120.00000 320.00000
A01 220.00000 350.00000 570.00000
A0101 150.00000 740.00000 890.00000
B 200.00000 620.00000 820.00000
B01 520.00000 280.00000 800.00000
B0101 500.00000 320.00000 820.00000
B0102 400.00000 120.00000 520.00000
*/
只能做到这里了。
[解决办法]
sum列数据没看懂 b0102的balance值没错误？
[解决办法]
sum 列数据没有看到，再描述一下！
[解决办法]
lz 尝试一下 group by with rollup
http://www.cnblogs.com/worfdream/articles/3071218.html
[解决办法]
if object_id('tempdb..#tmptbl') is not null
drop table #tmptbl
CREATE TABLE #tmptbl (accountcod VARCHAR(20),parentcod VARCHAR(20),credit NUMERIC(20,5), debit NUMERIC(20,5))
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A','',300,200)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A01','A',40,50)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A0101','A01',200,500)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B','',300,200)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B01','B',140,150)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B0101','B01',200,500)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B0102','B01',200,1000)
if object_id('tempdb..#tmptb2') is not null
drop table #tmptb2
select *,credit+debit as balance
into #tmptb2
from #tmptbl
;with t as
(
select *
from #tmptb2
union all
select t1.accountcod,t1.parentcod,t1.credit,t1.debit,t2.balance
from #tmptb2 t1 join t t2
on t1.accountcod=t2.parentcod
)
select accountcod,credit,debit,Sum(balance) as [sum]
from t
group by accountcod,credit,debit
accountcodcreditdebitsum
A300.00000200.000001290.00000
A0140.0000050.00000790.00000
A0101200.00000500.00000700.00000
B300.00000200.000002690.00000
B01140.00000150.000002190.00000
B0101200.00000500.00000700.00000
B0102200.000001000.000001200.00000
不知道楼主是不是要弄成酱紫