多到一的递归不好写,用临时表反向合计吧。CREATE TABLE table1( parent_id varchar(18), emp_id varchar(18), sum_ut_time real ) GO INSERT INTO table1 SELECT NULL,'010002911107->V1.3',12.5 UNION ALL SELECT '010002911107->V1.3','020005909572->V1.0',2.87 UNION ALL SELECT '010002911107->V1.3','020059081817->V1.3',6.5 UNION ALL SELECT '020059081817->V1.3','020004410218->V1.0',20 UNION ALL SELECT NULL,'010002919033->V1.0',29.87 UNION ALL SELECT '010002919033->V1.0','020059082897->V1.0',24.72 UNION ALL SELECT NULL,'010002923159->V1.0',89 GOSELECT *, sum_ut_time sum_all INTO #T FROM table1 WHERE NOT EXISTS (SELECT * FROM table1 c WHERE c.parent_id = table1.emp_id)WHILE @@ROWCOUNT<>0 BEGIN INSERT INTO #T SELECT p.*, p.sum_ut_time + (SELECT SUM(sum_all) FROM #T WHERE #T.parent_id = p.emp_id) FROM table1 p WHERE NOT EXISTS (SELECT * FROM #T WHERE #T.emp_id = p.emp_id) END SELECT * FROM #T ORDER BY emp_idDROP TABLE #T parent_id emp_id sum_ut_time sum_all ------------------ ------------------ ------------- ------------- NULL 010002911107->V1.3 12.5 15.37 NULL 010002919033->V1.0 29.87 54.59 NULL 010002923159->V1.0 89 89 020059081817->V1.3 020004410218->V1.0 20 20 010002911107->V1.3 020005909572->V1.0 2.87 2.87 010002911107->V1.3 020059081817->V1.3 6.5 26.5 010002919033->V1.0 020059082897->V1.0 24.72 24.72
急求,在SQL2000中,用递归求累加和!
这样的写法不算是递归,初学不太懂!请指教,下面这段不对,是从别的帖子上看到的! create function dbo.GetSumGongshi (@id_no as varchar(38)) returns numeric(22,8) as begin return( select sum_ut_time from sum_gongshi where bom_no=@id_no) + case when exists(select * from sum_gongshi where bom_no=@id_no) then (select sum(dbo.GetSumGongshi(id_no)) from sum_gongshi where id_no=@id_no) else 0 end end
http://blog.csdn.net/dotnetstudio/article/details/10109497
parent_id varchar(18),
emp_id varchar(18),
sum_ut_time real
)
GO
INSERT INTO table1
SELECT NULL,'010002911107->V1.3',12.5 UNION ALL
SELECT '010002911107->V1.3','020005909572->V1.0',2.87 UNION ALL
SELECT '010002911107->V1.3','020059081817->V1.3',6.5 UNION ALL
SELECT '020059081817->V1.3','020004410218->V1.0',20 UNION ALL
SELECT NULL,'010002919033->V1.0',29.87 UNION ALL
SELECT '010002919033->V1.0','020059082897->V1.0',24.72 UNION ALL
SELECT NULL,'010002923159->V1.0',89
GOSELECT *,
sum_ut_time sum_all
INTO #T
FROM table1
WHERE NOT EXISTS (SELECT *
FROM table1 c
WHERE c.parent_id = table1.emp_id)WHILE @@ROWCOUNT<>0
BEGIN
INSERT INTO #T
SELECT p.*,
p.sum_ut_time + (SELECT SUM(sum_all)
FROM #T
WHERE #T.parent_id = p.emp_id)
FROM table1 p
WHERE NOT EXISTS (SELECT *
FROM #T
WHERE #T.emp_id = p.emp_id)
END SELECT *
FROM #T
ORDER BY emp_idDROP TABLE #T
parent_id emp_id sum_ut_time sum_all
------------------ ------------------ ------------- -------------
NULL 010002911107->V1.3 12.5 15.37
NULL 010002919033->V1.0 29.87 54.59
NULL 010002923159->V1.0 89 89
020059081817->V1.3 020004410218->V1.0 20 20
010002911107->V1.3 020005909572->V1.0 2.87 2.87
010002911107->V1.3 020059081817->V1.3 6.5 26.5
010002919033->V1.0 020059082897->V1.0 24.72 24.72
create function dbo.GetSumGongshi
(@id_no as varchar(38))
returns numeric(22,8) as
begin
return(
select sum_ut_time from sum_gongshi where bom_no=@id_no) +
case
when exists(select * from sum_gongshi where bom_no=@id_no) then
(select sum(dbo.GetSumGongshi(id_no)) from sum_gongshi
where id_no=@id_no)
else 0
end
end