BOM逐级向上汇总应用实例

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

--  Author : htl258(Tony)

--  Date   : 2009-09-11 11:25:36

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   :http://blog.youkuaiyun.com/htl258

--  Subject:BOM逐级向上汇总

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

IF NOT OBJECT_ID('[tbTest]') IS NULL  

    DROP TABLE [tbTest]  

GO  

CREATE TABLE tbTest   

(  

  id nvarchar(5),   

  parentID nvarchar(5),   

  score decimal(18,2)   

)     

INSERT TBTEST   

SELECT '1','',10 UNION ALL   

SELECT '2','1',30 UNION ALL   

SELECT '3','2',30 UNION ALL   

SELECT '4','1',50 UNION ALL   

SELECT '5','',10 UNION ALL   

SELECT '6','5',30   

GO  

/* 

select * from tbTest  

id    parentID score 

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

1              10.00 

2     1        30.00 

3     2        30.00 

4     1        50.00 

5              10.00 

6     5        30.00 

 

(6 行受影响) 

*/ 

/* 

--要得到如下结果

id    parentid score 

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

1              130.00 

2     1        60.00 

3     2        30.00 

4     1        50.00 

5              40.00 

6     5        30.00 

 

(6 行受影响) 

*/ 

;WITH T AS 

(  

    SELECT *,total = score FROM tbTest AS a  

    WHERE NOT EXISTS(SELECT 1 FROM tbTest WHERE a.id = parentid)  

    UNION ALL  

    SELECT a.*,CAST(b.total + a.score AS decimal(18,2))  

    FROM tbTest AS a  

       JOIN t AS b  

    ON a.id = b.parentid  

)  

SELECT id,parentid,SUM(total) AS score   

from t   

GROUP BY id,parentid  

ORDER BY id  

/* 

id    parentid score 

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

1              130.00 

2     1        60.00 

3     2        30.00 

4     1        50.00 

5              40.00 

6     5        30.00 

 

(6 行受影响) 

 

*/ 

 

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

--  Author : htl258(Tony)

--  Date   : 2010-04-19 16:25:38

--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--  Blog   :http://blog.youkuaiyun.com/htl258

--  Subject:BOM逐级向上汇总（显示最底级）

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

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([ID] INT,[ParentID] INT,[Name] NVARCHAR(10))

INSERT [tb]

SELECT 1,0,'A' UNION ALL

SELECT 2,0,'B' UNION ALL

SELECT 3,1,'A.1' UNION ALL

SELECT 4,2,'B.1' UNION ALL

SELECT 5,4,'B.1.1' UNION ALL

SELECT 6,5,'B.1.1.1' UNION ALL

SELECT 7,1,'A.2'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

;WITH T AS

(

    SELECT *,N=0 FROM TB T

    WHERE NOT EXISTS(SELECT 1 FROM TB WHERE T.ID=[PARENTID])

    UNION ALL

    SELECT A.*,N+1 FROM TB A JOIN T B ON B.PARENTID=A.ID

)

SELECT Name,SUM(N) cnt

FROM T

WHERE N>0

GROUP BY NAME

 

/*

Name       cnt

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

A          2

B          3

B.1        2

B.1.1      1

 

(4 行受影响)

*/

 

 

--加缩进显示

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([ID] INT,[ParentID] INT,[Name] NVARCHAR(10))

INSERT [tb]

SELECT 1,0,'A' UNION ALL

SELECT 2,0,'B' UNION ALL

SELECT 3,1,'A.1' UNION ALL

SELECT 4,2,'B.1' UNION ALL

SELECT 5,4,'B.1.1' UNION ALL

SELECT 6,5,'B.1.1.1' UNION ALL

SELECT 7,1,'A.2'

GO

--SELECT * FROM [tb]

 

-->SQL查询如下:

;WITH T AS

(

    SELECT *,N=0 FROM TB T

    WHERE NOT EXISTS(SELECT 1 FROM TB WHERE T.ID=[PARENTID])

    UNION ALL

    SELECT A.*,N+1 FROM TB A JOIN T B ON B.PARENTID=A.ID

)

,T1 AS

(

    SELECT *,lvl=1 FROM TB T

    WHERE NOT EXISTS(SELECT 1 FROM TB WHERE ID=T.[PARENTID])

    UNION ALL

    SELECT A.*,lvl+1 FROM TB A JOIN T1 B ON A.PARENTID=B.ID

)

SELECT

    CASE B.lvl WHEN 1 THEN a.Name ELSE SPACE(B.lvl)+'┣'+a.Name END Name,

    Level=B.lvl,

    SUM(a.N) cnt

FROM T A

    JOIN T1 B

       ON A.N>0 AND B.ID=A.ID

GROUP BY B.lvl,A.Name

 

 

/*
Name         Level    cnt
A                1          2
B                1          3
  ┣B.1        2          2
   ┣B.1.1    3          1

(4 行受影响)
*/