树形结构分析汇总

t1

id     parentid

m    a

n    a

e    m

f    m

x    f

y    f

z    b

 

t2

row    id      amount

1    a    13.00

2    b    20.00

3    e    20.00

4    f    20.00

5    x    20.00

6    y    20.00

7    z    20.00

8    e    12.00

9    x    11.00

10    f    13.00

 

如何按照结构
a
--m
---e
---f
----x
----y
--n
b
--z

 

如何得出如下结果：

 

row     id      amount

7    x    20.00

11    x    11.00

    x小计   31.00

8    y    20.00

    y小计   20.00

6    f    20.00

12    f    13.00

    f小计   84.00

5    e    20.00

10    e    12.00

    e小计   32.00

3    m    14.00

    m小计   130.00

4    n    13.00

    n小计   13.00

1    a    13.00

    a小计   156.00

9    z    20.00

    z小计   20.00

2    b    20.00

    b小计   40.00

    总计   196.00

 

 

 

---------------------------------

--  Author: liangCK 小梁

---------------------------------

 

--> 生成测试数据: @tb1

DECLARE @tb1 TABLE (id VARCHAR(1),parentid VARCHAR(1))

INSERT INTO @tb1

SELECT 'm','a' UNION ALL

SELECT 'n','a' UNION ALL

SELECT 'e','m' UNION ALL

SELECT 'f','m' UNION ALL

SELECT 'x','f' UNION ALL

SELECT 'y','f' UNION ALL

SELECT 'z','b'

 

--> 生成测试数据: @tb2

DECLARE @tb2 TABLE (row INT,id VARCHAR(1),amount NUMERIC(4,2))

INSERT INTO @tb2

SELECT 1,'a',13.00 UNION ALL

SELECT 2,'b',20.00 UNION ALL

SELECT 3,'m',14.00 UNION ALL

SELECT 4,'n',13.00 UNION ALL

SELECT 5,'e',20.00 UNION ALL

SELECT 6,'f',20.00 UNION ALL

SELECT 7,'x',20.00 UNION ALL

SELECT 8,'y',20.00 UNION ALL

SELECT 9,'z',20.00 UNION ALL

SELECT 10,'e',12.00 UNION ALL

SELECT 11,'x',11.00 UNION ALL

SELECT 12,'f',13.00

 

--SQL查询如下:

 

--2005

;WITH Liang AS

(

    SELECT

        id,

        parentid,

        Ancestors=parentid,

        PATH=CAST(parentid+'.'+id AS VARCHAR(MAX))

    FROM @tb1 AS A

    WHERE NOT EXISTS(

              SELECT *

              FROM @tb1

              WHERE A.parentid=id

          )

    UNION ALL

    SELECT

        A.id,

        A.parentid,

        B.Ancestors,

        CAST(B.PATH+'.'+A.id AS VARCHAR(MAX))

    FROM @tb1 AS A

        JOIN Liang AS B

            ON A.parentid=B.id

),

Liang1 AS

(

    SELECT * FROM Liang

    UNION ALL

    SELECT DISTINCT

        parentid,

        parentid,

        Ancestors=parentid,

        PATH=CAST(parentid AS VARCHAR(MAX))

    FROM @tb1 AS A

    WHERE NOT EXISTS(

              SELECT *

              FROM @tb1

              WHERE A.parentid=id

          )

),

Liang2 AS

(

    SELECT

        A.id,

        A.parentid,

        A.Ancestors,

        A.PATH,

        B.row,

        B.amount,

        RID=DENSE_RANK()

              OVER(ORDER BY Ancestors,LEN(PATH)-LEN(REPLACE(PATH,'.','')) DESC,A.id)

    FROM Liang1 AS A

        JOIN @tb2 AS B

            ON A.id=B.id

),

Liang3 AS

(

    SELECT

        id,

        row,

        min(PATH) AS PATH,

        SUM(amount) AS amount,

        MIN(rid) AS rid,

        GROUPING(row) AS grow

    FROM Liang2

    GROUP BY id,row

    WITH ROLLUP

    HAVING GROUPING(id)=0

),

Liang4 AS

(

    SELECT

        *

    FROM Liang3 AS A

        OUTER APPLY (SELECT SUM(amount) sumamount

                     FROM Liang3

                     WHERE PATH LIKE A.PATH+'%'

                         AND grow=1) AS B

)

SELECT

    row,

    id,

    amount

FROM(

    SELECT

        CASE WHEN grow=1 THEN '' ELSE RTRIM(row) END AS row,

        CASE WHEN grow=1 THEN id+'小计' ELSE id END AS id,

        CASE WHEN grow=1 THEN sumamount ELSE amount END AS amount,

        rid

    FROM Liang4

    UNION ALL

    SELECT

        '','合计',SUM(amount),99999999

    FROM @tb2

) AS A

ORDER BY rid

 

/*

row          id    amount

------------ ----- ---------------------------------------

7            x     20.00

11           x     11.00

             x小计  31.00

8            y     20.00

             y小计  20.00

5            e     20.00

10           e     12.00

             e小计  32.00

6            f     20.00

12           f     13.00

             f小计  84.00

3            m     14.00

             m小计  130.00

4            n     13.00

             n小计  13.00

1            a     13.00

             a小计  156.00

9            z     20.00

             z小计  20.00

2            b     20.00

             b小计  40.00

             合计   196.00

 

(22 行受影响)

 

*/

 

--这是钻钻写的2000方法：

create function f_isChild(@id varchar(8),@cid varchar(8))

returns bit

as

begin

  if(@id=@cid)

    return 1

 

  declare @t table(id varchar(8),parentid varchar(8))

 

  insert into @t select * from t1 where id=@cid

 

  while @@rowcount>0

  begin

    if exists(select 1 from @t where parentid=@id)

      return 1

   

    insert into @t

    select b.* from @t a,t1 b

    where b.id=a.parentid and not exists(select 1 from @t where id=b.id)

  end

 

  return 0

end

go

 

 

create function f_getLevel(@id varchar(8))

returns varchar(40)

as

begin

  declare @var varchar(40),@parentid varchar(8)

  set @var=right('000'+@id,4)

  while exists(select 1 from t1 where id=@id)

  begin

    select @var=right('000'+parentid,4)+@var,@parentid=parentid from t1 where id=@id

    set @id=@parentid

  end

 

  return @var

end

go

 

select

    t.row,case when t.nid=2 then t.id+'小计' else t.id end as id,t.amount

from

  (select *,1 as nid from t2

    union all

    select

        null as row,a.id,sum(b.amount) as amount,2 as nid

    from

        (select distinct id from t2) a,t2 b

    where

        dbo.f_isChild(a.id,b.id)=1

    group by

        a.id

    union all

    select null as row,'总计',sum(amount),3 as nid from t2

    ) t

order by

    (case when nid=3 then 2 else 1 end),dbo.f_getLevel(t.id) desc,t.id,nid

go