一个离开.NET的程序员

ryhan
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
MS SQL-处理树型结构数据 将子节点记录的各项字段值累加赋值给其父节点

因最近开发数据分析作业，需要将将一个栏目的所有子栏目信息累加到其父节点上并在管理系统页面显示。由于在页面上运算会导致多次链接数据库，因此将数据在库内全部统计完成后，页面直接绑定。

1.首先上树型栏目数据表：

2.统计分析日志表-（需要按照上面的树型结构，将子节点的数据累加并更新到父节点）

3.上SQL-树型基础表

if exists (select * from sysobjects where id = OBJECT_ID('[Fact_ProgAndChProg]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [Fact_ProgAndChProg]

CREATE TABLE [Fact_ProgAndChProg] (
[ProgNo] [varchar]  (32) NOT NULL,
[FatherPorgNo] [varchar]  (32) NULL,
[IsExistsChProg] [char]  (1) NOT NULL,
[SortNo] [int]  NOT NULL)

INSERT [Fact_ProgAndChProg] ([ProgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1000',N'1',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[IsExistsChProg],[SortNo]) VALUES ( N'3000',N'1',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1001',N'1000',N'1',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1002',N'1001',N'0',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1003',N'1001',N'0',3)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1004',N'1001',N'0',4)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1005',N'1001',N'0',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1006',N'1001',N'0',5)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2000',N'1000',N'1',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2001',N'2000',N'0',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2002',N'2000',N'0',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2003',N'2000',N'0',3)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1007',N'1000',N'1',4)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1008',N'1007',N'0',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1009',N'1007',N'0',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1010',N'1007',N'0',4)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1011',N'1007',N'0',4)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1012',N'1007',N'0',5)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'3001',N'3000',N'0',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'3002',N'3000',N'0',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1015',N'1000',N'1',0)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1016',N'1015',N'0',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2008',N'1015',N'0',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2004',N'1000',N'1',0)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2005',N'2004',N'0',1)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2006',N'2004',N'0',2)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'2007',N'2004',N'0',3)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1014',N'1000',N'0',9)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'1013',N'1000',N'0',8)
INSERT [Fact_ProgAndChProg] ([ProgNo],[FatherPorgNo],[IsExistsChProg],[SortNo]) VALUES ( N'3003',N'3000',N'0',3)

4.上日志分析日志表

if exists (select * from sysobjects where id = OBJECT_ID('[Stat_Prog_DailyAnalyze]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [Stat_Prog_DailyAnalyze]

CREATE TABLE [Stat_Prog_DailyAnalyze] (
[DateID] [bigint]  NOT NULL,
[ProgNo] [varchar]  (32) NOT NULL,
[SvcCount] [bigint]  NOT NULL DEFAULT (0),
[SvcTime] [decimal]  (18,0) NOT NULL DEFAULT (0),
[FeeTime] [decimal]  (18,2) NOT NULL DEFAULT (0),
[InUserCount] [bigint]  NOT NULL DEFAULT (0),
[RingOrderFee] [decimal]  (18,2) NOT NULL DEFAULT (0),
[RingOrderCount] [bigint]  NOT NULL DEFAULT (0),
[RingOrderSucCount] [bigint]  NOT NULL DEFAULT (0),
[RingOrderFalCount] [bigint]  NOT NULL DEFAULT (0),
[CreateTime] [datetime]  NOT NULL DEFAULT (getdate()),
[CreateBy] [varchar]  (256) NOT NULL DEFAULT (''),
[UpdateTime] [datetime]  NOT NULL DEFAULT (getdate()),
[UpdateBy] [varchar]  (256) NOT NULL DEFAULT (''))

ALTER TABLE [Stat_Prog_DailyAnalyze] WITH NOCHECK ADD  CONSTRAINT [PK_Stat_Prog_DailyAnalyze] PRIMARY KEY  NONCLUSTERED ( [DateID],[ProgNo] )
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1000',7,45,0.75,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1001',7,23,0.38,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1002',1,0,0.00,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1003',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1004',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1005',6,876,14.60,1,5.00,1,1,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1006',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1007',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1008',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1009',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1010',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1011',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1012',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1013',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1014',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1015',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'1016',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2000',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2001',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2002',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2003',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2004',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2005',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2006',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2007',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'2008',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'3000',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'3001',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'3002',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130123,N'3003',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1000',9,22,0.37,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1001',9,156,2.60,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1002',1,93,1.55,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1003',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1004',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1005',9,577,9.62,1,3.50,2,1,1,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1006',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1007',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1008',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1009',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1010',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1011',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1012',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1013',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1014',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1015',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'1016',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2000',1,2,0.03,1,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2001',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2002',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2003',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2004',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2005',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2006',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2007',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'2008',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'3000',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'3001',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'3002',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')
INSERT [Stat_Prog_DailyAnalyze] ([DateID],[ProgNo],[SvcCount],[SvcTime],[FeeTime],[InUserCount],[RingOrderFee],[RingOrderCount],[RingOrderSucCount],[RingOrderFalCount],[CreateTime],[UpdateTime]) VALUES ( 20130124,N'3003',0,0,0.00,0,0.00,0,0,0,N'2013-1-29 9:48:25',N'2013-1-29 9:48:25')

 

6.按照想法实现的嵌套存储过程

  1 -- =============================================
  2 -- Author:        ryhan
  3 -- Create date: 2013.01.28
  4 -- Description:    递归更新各个栏目节点值
  5 -- =============================================
  6 CREATE PROCEDURE [dbo].[Stat_Job_UpdateProgData]
  7 (
  8      @DateID    BIGINT
  9     ,@ProgNo    VARCHAR(32)
 10     ,@FaProgNo    VARCHAR(32)
 11     ,@BizDBName    VARCHAR(128)
 12     ,@Result    INT OUT
 13 )
 14 AS
 15 BEGIN
 16 
 17     SET NOCOUNT ON
 18 
 19     SET @Result = 0
 20 
 21     DECLARE @SQLStr            VARCHAR(1024)    
 22 
 23     DECLARE @ProgCountTab    TABLE(ChCount INT)
 24     DECLARE @ProgCount        INT
 25 
 26     SET @SQLStr ='SELECT COUNT(ProgNo) FROM '+@BizDBName+'.[Common_ProgAndProgAssociation] WHERE FatherPorgNo = '''+@ProgNo+''' '
 27     INSERT INTO @ProgCountTab EXEC(@SQLStr)
 28     SELECT @ProgCount = ChCount FROM @ProgCountTab
 29     
 30     SET NOCOUNT OFF
 31 
 32     IF @ProgCount > 0
 33     BEGIN
 34         SET NOCOUNT ON
 35 
 36         DECLARE @ChProgNoList    TABLE(ProgNo VARCHAR(32))
 37         DECLARE @ChProgNo        VARCHAR(32)
 38         DECLARE @ChProgCount    INT
 39 
 40         --获取@ProgNo的子节点列表@ChProgNoList、总数@ChProgCount
 41         SET @SQLStr = 'SELECT ProgNo FROM '+@BizDBName+'.[Common_ProgAndProgAssociation] WHERE FatherPorgNo = '''+@ProgNo+''' '
 42         INSERT INTO @ChProgNoList EXEC(@SQLStr)
 43         SELECT @ChProgCount = COUNT(ProgNo) FROM @ChProgNoList
 44         
 45         --循环@ProgNo的子节点列表
 46         WHILE @ChProgCount > 0
 47         BEGIN
 48             --获取@ProgNo的一个子节点@ChProgNo
 49             SELECT TOP 1 @ChProgNo = ProgNo FROM @ChProgNoList
 50             --PRINT '$'+ @FaProgNo +'_$'+ @ProgNo +'_$'+ @ChProgNo
 51 
 52             --递归@ProgNo的子节点@ChProgNo            
 53             EXEC Stat_Job_UpdateProgData @DateID,@ChProgNo,@ProgNo,@BizDBName,@Result            
 54 
 55             --递归完成后，将子节点@ChProgNo从列表@ChProgNoList中移除
 56             --并将列表总数@ChProgCount减 1
 57             DELETE FROM @ChProgNoList WHERE ProgNo = @ChProgNo
 58             SET @ChProgCount = @ChProgCount - 1
 59 
 60             --循环到最后一条记录时，将节点@ProgNo所有子节点的数据求和，将求和结
 61             --果与@ProgNo本身的的数据相加，并将最终的数据赋值给@ProgNo
 62             IF @ChProgCount = 0
 63             BEGIN                
 64                 SET @SQLStr = 'SELECT ProgNo FROM '+@BizDBName+'.[Common_ProgAndProgAssociation] WHERE FatherPorgNo = '''+@ProgNo+''' '
 65                 INSERT INTO @ChProgNoList EXEC(@SQLStr)        
 66                 
 67                 UPDATE FaProg
 68                 SET 
 69                      FaProg.SvcCount = FaProg.SvcCount + ChProg.SvcCount
 70                     ,FaProg.SvcTime = FaProg.SvcTime + ChProg.SvcTime
 71                     ,FaProg.FeeTime = FaProg.FeeTime + ChProg.FeeTime
 72                     --,FaProg.InUserCount = FaProg.InUserCount + ChProg.InUserCount
 73                     ,FaProg.RingOrderFee = FaProg.RingOrderFee + ChProg.RingOrderFee
 74                     ,FaProg.RingOrderCount = FaProg.RingOrderCount + ChProg.RingOrderCount
 75                     ,FaProg.RingOrderSucCount = FaProg.RingOrderSucCount + ChProg.RingOrderSucCount
 76                     ,FaProg.RingOrderFalCount = FaProg.RingOrderFalCount + ChProg.RingOrderFalCount
 77                 FROM 
 78                     (
 79                         SELECT 
 80                              DateID
 81                             ,SUM(SvcCount) SvcCount,SUM(SvcTime) SvcTime
 82                             --,SUM(InUserCount) InUserCount
 83                             ,SUM(RingOrderFee) RingOrderFee,SUM(FeeTime) FeeTime
 84                             ,SUM(RingOrderCount) RingOrderCount,SUM(RingOrderSucCount) RingOrderSucCount
 85                             ,SUM(RingOrderFalCount) RingOrderFalCount
 86                         FROM
 87                             Stat_Prog_DailyAnalyze
 88                         WHERE
 89                             DateID = @DateID 
 90                             AND ProgNo IN (SELECT ProgNo FROM @ChProgNoList)
 91                         GROUP BY
 92                             DateID
 93                     ) ChProg
 94                     ,Stat_Prog_DailyAnalyze FaProg
 95                 WHERE
 96                     FaProg.DateID = ChProg.DateID
 97                     AND FaProg.ProgNo = @ProgNo                
 98                     SELECT ProgNo FROM @ChProgNoList
 99             END            
100         END        
101         SET NOCOUNT OFF    
102     END
103     ELSE
104     BEGIN
105         RETURN
106     END
107     SET @Result = 1
108 END

 

7.进行调用

DECLARE @Result INT
EXEC [Stat_Job_UpdateProgData] 20130123,'','',@Result

8.运算后的结果

--记录下来备用 ：by：ryhan  2013.01.29

FROM:http://www.cnblogs.com/ryhan/archive/2013/01/29/2880945.html

posted on 2013-01-29 10:15  ryhan  阅读(1228)  评论(2)  编辑  收藏  举报