请教一个 树形统计问题

alan

有两个表，一个记录树形结构，如下
ID    ParentID
01
02    01
03    01
04    02
05    03

另一个表记录数据，如下
ID   AMT
01   100
02   300
04   500
05   200

我想得到最终结果是：
ID   AMT
01   1100
02   800
03   200
04   500
05   200  

目前能想到的方案是 递归遍历树，得到结果。
请问有没有用一句或几句sql，就能返回结果集的办法，谢谢

dalaohu

树形结构, 递归遍历树... 如此深奧的詞。。。。 你那裡學來的？

不就是個 inner join 嗎。

alan

原帖由 dalaohu 于 2010-10-6 11:59 发表 [url=http://www.oursteps.com.au/bbs/redirect.php?goto=findpost&pid=4657904&ptid=273659]

不就是個 inner join 嗎。

求教怎么写了，谢谢

乱码

如果你在做childtree（包括本身node）的sum， cte is the way u should look into.

http://bytes.com/topic/sql-serve ... e-sql-server-2005-a

if u r unfamiliar with cte recursion, google it.

http://www.sqlservercentral.com/ ... sqlserver2005/1760/

http://www.mssqltips.com/tip.asp?tip=1520

[ 本帖最后由 乱码 于 2010-10-6 12:16 编辑 ]

ambition

原帖由 dalaohu 于 2010-10-6 11:59 发表
树形结构, 递归遍历树... 如此深奧的詞。。。。 你那裡學來的？

不就是個 inner join 嗎。

错了， 他的表１是个树型结构的记录表，所以不是单纯的inner join的关系。

SQL是不够，用PL／SQL吧

o2h2o

这个需要 递归的
可以去查一下
recursive CTE( 如果是 sql server)

ambition

原帖由 乱码 于 2010-10-6 12:12 发表
如果你在做childtree（包括本身node）的sum， cte is the way u should look into.

http://bytes.com/topic/sql-serve ... e-sql-server-2005-a

if u r unfamiliar wi ...

土了，很久没接触DB了，原来SQL也可以 handle tree啊。

乱码

原帖由 ambition 于 2010-10-6 12:19 发表

土了，很久没接触DB了，原来SQL也可以 handle tree啊。

嗯，cte是sql server 2005的一个feature，它的recursion只是其中一个应用，更多的应用应该是保存中间结果（不用temp table/table variable,因为它们有temp db的io),给以后的query提供一个memory中的缓存，好的地方是很多复杂的logic都可以借助cte完成（原来我做的最多的cte，一个query有13个），不好的地方就是很难通过profiler看到中间结果，很多triky的地方需要很好的sql skills.

CTE近两年在sql server community应用非常多，基本上不熟悉就没法混了。

alan

谢谢你的答复，我再学习一下你推荐的帖子

TuLaLa

题目满有趣的，想了一下，假设两个table分别叫table_1 和 table_2可以这样实现：

;WITH TopParent(ID, TopParentId)
AS
(
        SELECT
                t1.ID, t0.ID
        FROM
                TABLE_1 t0
                INNER JOIN Table_1 t1 ON t1.ParentId = t0.ID
        WHERE
                t0.ParentId IS NULL
        UNION ALL
        SELECT
                t2.ID, t3.TopParentId
        FROM
                Table_1 t2 INNER JOIN TopParent t3 ON t2.ParentId = t3.ID
)
SELECT * FROM TopParent

bffbffbff

create table #a (Id int, parentID int )
create table #b (Id int, AMT int )


insert into #a values (1, null)
insert into #a values (2, 1)
insert into #a values (3, 1)
insert into #a values (4, 2)
insert into #a values (5, 3)

insert into #b values (1, 100)
insert into #b values (2, 300)
insert into #b values (4, 500)
insert into #b values (5, 200)


;WITH Tem(NId, CId, AMT)
AS
(
        SELECT a.id AS Nid, a.id AS CId, ISNULL(b.AMT, 0) AS AMT       
        FROM #a a
        LEFT JOIN #b b on a.id = b.id

        UNION ALL

        SELECT  t.Nid, a2.Id AS CId,isnull( (SELECT b2.AMT FROM #b b2 WHERE a2.Id = b2.Id) , 0) AS AMT                        
        FROM Tem t
        JOIN #a a2 ON isnull(a2.parentId, 0) = t.CId
)


select NId, sum(AMT) as AMT
from Tem         
group by NId


drop table #a
drop table #b

[ 本帖最后由 bffbffbff 于 2010-10-6 13:30 编辑 ]

alan

谢谢，谢谢。我正看着推荐的相关帖子呢，您的代码就写出来了。我试了，完全正确。那我就直接研究您的代码了。

同时也谢谢以上各位同志的帮忙。

bffbffbff

原帖由 alan 于 2010-10-6 13:50 发表
谢谢，谢谢。我正看着推荐的相关帖子呢，您的代码就写出来了。我试了，完全正确。那我就直接研究您的代码了。

同时也谢谢以上各位同志的帮忙。 ...

只限于你楼顶给出的例子，如果数据变复杂了或者其他你没提到的情况下代码不工作不负责 ， 不过这个例子你在稍微改动下也应该可以用了
我也是用了cte不到1年而且多是为了中间存储，看到有个recursion的东东就自己试下好玩了

TuLaLa

看错搂主的要求了，原来不是找top most parent. 改了一下原来的query.

DECLARE @t1 TABLE(Id int, parentID int)
DECLARE @t2 TABLE(Id int, AMT int)

INSERT INTO @t1 VALUES (1, null)
INSERT INTO @t1 VALUES (2, 1)
INSERT INTO @t1 VALUES (3, 1)
INSERT INTO @t1 VALUES (4, 2)
INSERT INTO @t1 VALUES (5, 3)

INSERT INTO @t2 VALUES (1, 100)
INSERT INTO @t2 VALUES (2, 300)
INSERT INTO @t2 VALUES (4, 500)
INSERT INTO @t2 VALUES (5, 200)

;WITH TEMP( Parent, Child, AMT)
AS
(
        SELECT
                t0.ParentId, t0.Id, ISNULL(t1.AMT,0)
        FROM
                @t1 t0 LEFT JOIN @t2 t1 ON t1.Id = t0.Id
        UNION ALL
        SELECT
                t5.Parent, t4.Id, ISNULL(t3.AMT, 0)
        FROM
                @t2 t3
                INNER JOIN @t1 t4 ON t3.Id = t4.Id
                INNER JOIN TEMP t5 ON t4.ParentId = t5.Child
)
SELECT
        ISNULL(Parent,Child), SUM(AMT)
FROM
        TEMP
GROUP BY
        ISNULL(Parent,Child)

[ 本帖最后由 TuLaLa 于 2010-10-6 14:47 编辑 ]

bffbffbff

原帖由 TuLaLa 于 2010-10-6 14:45 发表
看错搂主的要求了，原来不是找top most parent. 改了一下原来的query.

DECLARE @t1 TABLE(Id int, parentID int)
DECLARE @t2 TABLE(Id int, AMT int)

INSERT INTO @t1 VALUES (1, null)
INSERT INTO @t1 VALUES (2, ...

缺了最后一个item的结果（05   200）
，逻辑上有点问题，id 3和5的算法好像都不太对。。。

alan

确实是这样。如果第二张表的记录double一遍，即再执行一遍
insert into #b values (1, 100)
insert into #b values (2, 300)
insert into #b values (4, 500)
insert into #b values (5, 200)

那么您的句子，会出问题。 我还没完全理解CTE，所以不知怎样改，在继续研究。。。

bffbffbff

原帖由 alan 于 2010-10-6 15:15 发表
确实是这样。如果第二张表的记录double一遍，即再执行一遍
insert into #b values (1, 100)
insert into #b values (2, 300)
insert into #b values (4, 500)
insert into #b values (5, 200)

那么您的句子，会出问题。 ...

这个倒不复杂，没什么变化，只不过Id不是key，记录变重复了，你只要在call cte之前把数据给合并一下就好了， 不过合并的时候不一定总是sum（这里的例子我猜你是想计算总和）， 有时候也可能是avg， max等等，得根据要求来运算


create table #a (Id int, parentID int )
create table #b (Id int, AMT int )


insert into #a values (1, null)
insert into #a values (2, 1)
insert into #a values (3, 1)
insert into #a values (4, 2)
insert into #a values (5, 3)

insert into #b values (1, 100)
insert into #b values (2, 300)
insert into #b values (4, 500)
insert into #b values (5, 200)

insert into #b values (1, 100)
insert into #b values (2, 300)
insert into #b values (4, 500)
insert into #b values (5, 200)



create table #b2 (Id int, AMT int )

insert into #b2
select Id, sum(isnull(AMT, 0))
from #b
group by Id


;WITH Tem(NId, CId, AMT)
AS
(
        SELECT a.id AS Nid, a.id AS CId,ISNULL(b.AMT, 0) AS AMT        
        FROM #a a
        LEFT JOIN #b2 b on a.id = b.id


        UNION ALL

        SELECT  t.Nid, a2.Id AS CId, isnull( (SELECT b2.AMT FROM #b2 b2 WHERE a2.Id = b2.Id) , 0) AS AMT                        
        FROM Tem t
        JOIN #a a2 ON isnull(a2.parentId, 0) = t.CId

)


select NId, sum(AMT) as AMT
from Tem         
group by NId


drop table #a
drop table #b
drop table #b2

[ 本帖最后由 bffbffbff 于 2010-10-6 15:34 编辑 ]