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