我查了下,问这个问题的人比较多,但是好像都不是我要的那种
表结构数据如下: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','',200,120)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A01','A',220,350)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A0101','A01',150,740)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B','',200,620)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B01','B',520,280)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B0101','B01',500,320)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B0102','B01',400,120)
SELECT * FROM #tmptbl
需要输出至水晶报表,效果图如下
如果有什么好的思路请跟贴告诉我,谢谢。
另,如果还没描述清楚请跟贴说明,我在线等待。SQL报表select
表结构数据如下: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','',200,120)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A01','A',220,350)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('A0101','A01',150,740)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B','',200,620)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B01','B',520,280)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B0101','B01',500,320)
INSERT INTO #tmptbl (accountcod,parentcod,credit,debit) VALUES ('B0102','B01',400,120)
SELECT * FROM #tmptbl
需要输出至水晶报表,效果图如下
如果有什么好的思路请跟贴告诉我,谢谢。
另,如果还没描述清楚请跟贴说明,我在线等待。SQL报表select
子帐户的B的sum=B01的sum + B的balance 以此类推
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
*/只能做到这里了。
http://www.cnblogs.com/worfdream/articles/3071218.html
谢谢daiyueqiang,谢谢tangguangqiang,谢谢几位回复的
if object_id('tempdb..#tmptbl') is not null
drop table #tmptblCREATE 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,debitaccountcod credit debit sum
A 300.00000 200.00000 1290.00000
A01 40.00000 50.00000 790.00000
A0101 200.00000 500.00000 700.00000
B 300.00000 200.00000 2690.00000
B01 140.00000 150.00000 2190.00000
B0101 200.00000 500.00000 700.00000
B0102 200.00000 1000.00000 1200.00000不知道楼主是不是要弄成酱紫