你的位置:首页 > 软件开发 > 数据库 > sql server: sql script

sql server: sql script

发布时间:2015-12-07 17:00:32
select ProductGUID,ProductName,ProjectGUID from dbo.Product/*F637A079-E22B-4E50-87E9-000147B1B1F4产品5 4CB304EF-2135-43E7-90D6-B03B75CB491B C7 ...
select ProductGUID,ProductName,ProjectGUID from dbo.Product/*F637A079-E22B-4E50-87E9-000147B1B1F4产品5	4CB304EF-2135-43E7-90D6-B03B75CB491B	C731BDB9-1436-4A23-A4FA-001097DF8218产品4	471C3F21-B725-497C-9383-6ED7C97756D8	4CF14E4B-495F-4344-801F-001D4C731494产品1	0BF32124-1963-4A5A-920C-036B2A0A2186	71344D5D-9994-4DC7-ABF6-00546C11565C产品3	A7E60BCF-1FA0-4D00-AE36-50C61074119F	F182ED1B-DACB-43A0-958B-005C5174429F产品3	645E2033-9CE8-44E3-89E8-F7638E6108CB	76C87E67-E3DE-4D0D-8256-005DADE4118D产品5	F85302F9-31A4-447B-A0CE-9F5586B5AB4C	76F4E739-9EE5-4570-B93E-00620E6954A2产品2	4815811E-9F9C-46B1-AA46-8A98A967F233	494A2EBD-D414-4470-A9A8-006346620755产品5	CD6CA57C-6398-4C4B-91A4-9C5A22C0991D	D8EC93CE-D3E6-48CE-B276-006D7FE1F9FC产品3	94B1FF27-49F7-4196-BA89-88AF71F27C49	C2EB45AB-5C0D-43E1-82F4-00795920FF1D产品4	BCBD7C06-D963-44B6-AF79-550831B1CE53	5BB124D2-6748-4514-992D-009AD0C01DD6产品1	EED22E55-FB42-42AE-9919-8D13BCF47506	29C1840B-E73F-4BF4-BE62-00E8A3EB8D95产品5	83E68000-94B8-42DD-BE71-BC3AF1A11677	*/select ProjectGUID,ProjectName from dbo.Project/*F1C24DA5-072A-40E4-8451-0081FF5B0678项目286	2158A228-F248-46AA-98FB-008F84A183A4项目187	582B7C29-D7BA-4FA3-BE26-01AE3973C2AF项目122	D8F179DF-9844-4CF8-AFAA-01C64296A14E项目95	8EE3A6A8-D73F-45C5-B09F-024214D20043项目62	AD86DBC2-AA91-4940-9C27-02B5734C3EC6项目367	2468F381-C1F9-4E0B-B0DF-032063BCAAC4项目70	0BF32124-1963-4A5A-920C-036B2A0A2186项目336	1E75C1E0-2829-43C8-9031-04624215C738项目377	21B31D71-0678-4229-80A8-0539023F9F6F项目4	15ED25B3-103A-415E-AF91-058BE0AE0600项目8	EE4F7336-9629-425B-ABCF-0662F01EEECB项目470	9374A1E0-54F8-49C7-A02C-0675F46E153D项目63	1CE94186-510F-4B6B-BE4B-06C9362951EF项目137	FC1DDDAB-3B92-424A-856C-082DEB91A1B3项目9	3F1AA58F-28A3-402F-9CE7-086C26998C17项目210	08601BA9-767F-4F92-941F-08C9EBFCA3A4项目466	*/--SaleNum: 套数(INT)--SaleArea:面积(MONEY)--SalePrice:单价(MONEY)--SaleAmount:销售中加(MONEY)select SaleDtlGUID,ProductGUID,YearMonth,SaleNum,SaleArea,SalePrice,SaleAmount,[Year],[Month] from dbo.SaleDtl/*D9023E32-D981-4DB1-82EA-A8296FBF6A8AF637A079-E22B-4E50-87E9-000147B1B1F4	2013-04  	7	700.0000	6000.0000	4200000.0000	2013	04	BEDF4CED-0C39-45EA-97BF-7C298C1D8A5BF637A079-E22B-4E50-87E9-000147B1B1F4	2008-11  	9	900.0000	5000.0000	4500000.0000	2008	11	63DE92BA-4C08-4FF1-9A68-27CD4293084AF637A079-E22B-4E50-87E9-000147B1B1F4	2014-08  	12	1200.0000	7000.0000	8400000.0000	2014	08	BC874228-B13F-4E49-9560-3CD39FF2B8A9F637A079-E22B-4E50-87E9-000147B1B1F4	2011-05  	12	12000.0000	7000.0000	84000000.0000	2011	05	164FA80F-3ECF-4E82-86A1-A7F832867028F637A079-E22B-4E50-87E9-000147B1B1F4	2011-04  	7	700.0000	6000.0000	4200000.0000	2011	04	7EC32C1E-F34B-4BF1-9BA4-5205C2C322F0F637A079-E22B-4E50-87E9-000147B1B1F4	2010-05  	12	12000.0000	7000.0000	84000000.0000	2010	05	278D72A4-F340-4DB9-8083-7BD277EB7C9EF637A079-E22B-4E50-87E9-000147B1B1F4	2013-11  	9	900.0000	5000.0000	4500000.0000	2013	11	E70EF3D1-A92C-45C1-A7D3-110A57C93CF0F637A079-E22B-4E50-87E9-000147B1B1F4	2015-08  	12	1200.0000	7000.0000	8400000.0000	2015	08	26D1A828-26EE-4B9E-BEEF-A6F0A74CDE9EF637A079-E22B-4E50-87E9-000147B1B1F4	2007-02  	8	800.0000	3500.0000	2800000.0000	2007	02	FD59AF4A-447D-4860-B2C2-10B0FD8C7531F637A079-E22B-4E50-87E9-000147B1B1F4	2006-03  	5	500.0000	5000.0000	2500000.0000	2006	03	9288807A-C53A-44FA-B9AF-A69E0BF1C4CEF637A079-E22B-4E50-87E9-000147B1B1F4	2006-02  	8	800.0000	3500.0000	2800000.0000	2006	02	CF001CD9-BC4D-4FB8-B39E-0FE41D2468A8F637A079-E22B-4E50-87E9-000147B1B1F4	2011-08  	12	1200.0000	7000.0000	8400000.0000	2011	08	608070DC-53F0-46E0-B5F8-102106786073F637A079-E22B-4E50-87E9-000147B1B1F4	2012-04  	7	700.0000	6000.0000	4200000.0000	2012	04	9869C12C-0896-4C17-B575-3A2C52CBCE13F637A079-E22B-4E50-87E9-000147B1B1F4	2014-10  	3	300.0000	5000.0000	1500000.0000	2014	10	705F790B-23F3-4C9D-A9DA-A4B5799D1D56F637A079-E22B-4E50-87E9-000147B1B1F4	2007-01  	10	1000.0000	4000.0000	4000000.0000	2007	01	050DA0EE-8862-4F0F-92FE-A521208F09C3F637A079-E22B-4E50-87E9-000147B1B1F4	2009-12  	6	600.0000	5000.0000	3000000.0000	2009	12	6E0903D3-204E-4E3E-A737-39FCF7B434E8F637A079-E22B-4E50-87E9-000147B1B1F4	2010-08  	12	1200.0000	7000.0000	8400000.0000	2010	08	22A0FC81-77C1-4B37-9DA8-24166AF7A490F637A079-E22B-4E50-87E9-000147B1B1F4	2011-07  	8	800.0000	8000.0000	6400000.0000	2011	07	DDBDE42E-4986-44AC-BE77-8EC381356820F637A079-E22B-4E50-87E9-000147B1B1F4	2014-11  	9	900.0000	5000.0000	4500000.0000	2014	11	6A7B23A3-C48B-4519-986D-3901CCFA50AEF637A079-E22B-4E50-87E9-000147B1B1F4	2010-04  	7	700.0000	6000.0000	4200000.0000	2010	04	1BA93EC8-CB89-4DA5-B27C-239A43099214F637A079-E22B-4E50-87E9-000147B1B1F4	2009-04  	7	700.0000	6000.0000	4200000.0000	2009	04	*//*临时表说明#product:用项目过滤后,将“合计”作为一个产品的集合#TempAllSaleDtl:通过项目过滤后的销售明细,所有月的#ProductSaleArea:各个产品的总面积,用于计算比例#TempSaleDtl:通过日期过滤,且加工过后的销售明细,包括增加累积列,以前年度、以后年度、项目合计的记录#tempSaleDtl2:列转行后的数据集#tempSaleDtl3:行转列后的数据集*/DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份--查找该项目的所有产品放进临时表#product,这里将“合计”作为一个产品的集合也插入产品表#product:select ProductGUID,ProductName,ProjectGUID,ProductCode into #product from(select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCode from Product where ProjectGUID=@ProjectGUID union all select '00000000-0000-0000-0000-000000000000','合计',@ProjectGUID,'00' as ProductCode) aGO--查找该项目的所有产品的销售明细放进临时表#TempAllSaleDtl,以作备用:DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtl FROM dbo.SaleDtl WHERE ProductGUID IN (SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID)go--根据#TempAllSaleDtl现有数据统计,向#TempAllSaleDtl添加总合计记--根据现有数据统计,向#TempAllSaleDtl添加总合计记录insert into #TempAllSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice)select '00000000-0000-0000-0000-000000000000',YearMonth,SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea)from #TempAllSaleDtl group by YearMonthgo--从临时表#TempAllSaleDtl 中查找指定年的销售明细放进临时表#TempSaleDtl ,注意 这个时候就已经包含了 “合计”产品00的数据:--查找某年的销售明细:#TempSaleDtlDECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,SalePrice AS ljSaleArea,SalePrice AS blSaleArea,SalePrice AS ljSaleAmountINTO #TempSaleDtl FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Yeargo--从临时表#TempAllSaleDtl 中查找指定年的销售明细放进临时表#TempSaleDtl ,注意 这个时候就已经包含了 “合计”产品00的数据:--查找某年的销售明细:#TempSaleDtlDECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,SalePrice AS ljSaleArea,SalePrice AS blSaleArea,SalePrice AS ljSaleAmount INTO #TempSaleDtl FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Yeargo--从#TempAllSaleDtl 中统计项目各个产品的总销售面积放入表:#ProductSaleArea,主要用作计算 项目累计销售面积比例--获取项目各个产品的总销售面积:#ProductSaleAreaSELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUID go--从表#TempSaleDtl 统计当前年度合计列,各产品的所有面积、金额、均价总合计 放入表#TempSaleDtl,注意这里 SUM(SaleAmount)/SUM(SaleArea) 计算销售单价:--添加2011合计列的记录(本年度的各产品的所有面积、金额、均价总合计)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,@Year+'-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0from #TempSaleDtl group by ProductGUID GO--从表#TempAllSaleDtl 统计以前年度的各产品的所有面积、金额、均价总合计 操作与上一步类似 放入表#TempSaleDtl--以前年度列记录(本年度以前的各产品的所有面积、金额、均价总合计 操作与上一步类似)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,@Year+'-00',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl where YearMonth=@Year+'-00'group by ProductGUID GO--从表#TempAllSaleDtl 统计以后年度的各产品的所有面积、金额、均价总合计 操作与上一步类似 放入表#TempSaleDtl--以后年度列记录(本年度以后的各产品的所有面积、金额、均价总合计 操作与上一步类似)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,'9999-12',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl where YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00'group by ProductGUID go--从表#TempAllSaleDtl 统计各产品取所有的合计 放入表#TempSaleDtl--项目合计列记录(各产品取所有的合计。与上面的区别在于没有添加 here YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00')insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,'9999-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl group by ProductGUID GO--从表#TempSaleDtl 与 #TempAllSaleDtl统计累积销售面积、累积销售面积比例,累积销售金额 更新表#TempSaleDtl--更新销售明细TempSaleDtl的累积销售面积、累积销售面积比例,累积销售金额UPDATE #TempSaleDtl SET ljSaleArea=b.sum_SaleArea,ljSaleAmount=b.sum_SaleAmount,blSaleArea=b.sum_SaleArea/c.all_SaleArea FROM #TempSaleDtl left JOIN (SELECT n.ProductGUID,n.YearMonth,SUM(m.SaleArea) AS sum_SaleArea,SUM(m.SaleAmount) AS sum_SaleAmount FROM #TempAllSaleDtl m INNER JOIN #TempSaleDtl n ON m.YearMonth=n.YearMonth AND m.ProductGUID=n.ProductGUID GROUP BY n.ProductGUID,n.YearMonth) b ON #TempSaleDtl.ProductGUID=b.ProductGUID AND #TempSaleDtl.YearMonth=b.YearMonth LEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUID GO--从表#TempSaleDtl 列转行,转换后的表只有 产品、统计类型、日期,值4列;(每个产品对应的0-12、13 月对应的值) 放入表#tempSaleDtl2--列转行,转换后的表只有 产品、统计类型、日期,值4列;(每个产品对应的0-12、13 月对应的值)SELECT * INTO #tempSaleDtl2 FROM (SELECT ProductGUID,'销售套数' AS type,'01' AS typecode,YearMonth,MAX(SaleNum) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'销售面积' AS type,'02' AS typecode,YearMonth,MAX(SaleArea) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'销售均价' AS type,'03' AS typecode,YearMonth,MAX(SalePrice) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'销售金额' AS type,'04' AS typecode,YearMonth,MAX(SaleAmount) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'累计销售面积' AS type, '05' AS typecode,YearMonth,SUM(ljSaleArea) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'累计销售面积比例' AS type, '06' AS typecode,YearMonth,SUM(blSaleArea) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'累计销售金额' AS type, '07' AS typecode,YearMonth,SUM(ljSaleAmount) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth) t GO--从表#tempSaleDtl2行转列,按类型聚合 求出每个产品每个类型(面积、金额……)的合计 放入表#tempSaleDtl3]DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份SELECT ProductGUID,type,typecode,  MAX(CASE YearMonth WHEN '9999-13' THEN val ELSE 0 END) AS '项目合计',  MAX(CASE YearMonth WHEN @Year+'-00' THEN val ELSE 0 END) AS '以前年度合计',  MAX(CASE YearMonth WHEN @Year+'-13' THEN val ELSE 0 END) AS '2011年合计',  MAX(CASE YearMonth WHEN @Year+'-01' THEN val ELSE 0 END) AS '2011-01',  MAX(CASE YearMonth WHEN @Year+'-02' THEN val ELSE 0 END) AS '2011-02',  MAX(CASE YearMonth WHEN @Year+'-03' THEN val ELSE 0 END) AS '2011-03',  MAX(CASE YearMonth WHEN @Year+'-04' THEN val ELSE 0 END) AS '2011-04',  MAX(CASE YearMonth WHEN @Year+'-05' THEN val ELSE 0 END) AS '2011-05',  MAX(CASE YearMonth WHEN @Year+'-06' THEN val ELSE 0 END) AS '2011-06',  MAX(CASE YearMonth WHEN @Year+'-07' THEN val ELSE 0 END) AS '2011-07',  MAX(CASE YearMonth WHEN @Year+'-08' THEN val ELSE 0 END) AS '2011-08',  MAX(CASE YearMonth WHEN @Year+'-09' THEN val ELSE 0 END) AS '2011-09',  MAX(CASE YearMonth WHEN @Year+'-10' THEN val ELSE 0 END) AS '2011-10',  MAX(CASE YearMonth WHEN @Year+'-11' THEN val ELSE 0 END) AS '2011-11',  MAX(CASE YearMonth WHEN @Year+'-12' THEN val ELSE 0 END) AS '2011-12',  MAX(CASE YearMonth WHEN '9999-12' THEN val ELSE 0 END) AS '以后年度合计'into #tempSaleDtl3 FROM #tempSaleDtl2 GROUP BY ProductGUID,type,typecode ORDER BY ProductGUID,typecodeGOselect * from #tempSaleDtl3GO--从Project表中加入项目数据DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份select ProjectName as orderCode,ProjectGUID,ProjectName,    '--' AS '项目合计',    '--' AS '以前年度合计',    '--' AS '2011年合计',    '--' AS '2011-01',    '--' AS '2011-02',    '--' AS '2011-03',    '--' AS '2011-04',    '--' AS '2011-05',    '--' AS '2011-06',    '--' AS '2011-07',    '--' AS '2011-08',    '--' AS '2011-09',    '--' AS '2011-10',    '--' AS '2011-11',    '--' AS '2011-12',    '--' AS '以后年度合计'  from Project  where ProjectGUID=@ProjectGUIDGOselect Project.ProjectName+'.'+a.ProductCode as orderCode,a.ProductGUID,a.ProductName,    '--' AS '项目合计',    '--' AS '以前年度合计',    '--' AS '2011年合计',    '--' AS '2011-01',    '--' AS '2011-02',    '--' AS '2011-03',    '--' AS '2011-04',    '--' AS '2011-05',    '--' AS '2011-06',    '--' AS '2011-07',    '--' AS '2011-08',    '--' AS '2011-09',    '--' AS '2011-10',    '--' AS '2011-11',    '--' AS '2011-12',    '--' AS '以后年度合计'  from #product a  left join Project on a.ProjectGUID=Project.ProjectGUIDGO--从产品表和Project表、#tempSaleDtl3中加入类型行数据  select c.ProjectName+'.'+b.ProductCode+'.'+a.typecode as orderCode,a.ProductGUID, a.[type],    cast(a.[项目合计] as varchar(20)),    cast(a.[以前年度合计] as varchar(20)),    cast(a.[2011年合计] as varchar(20)),    cast(a.[2011-01] as varchar(20)),    cast(a.[2011-02] as varchar(20)),    cast(a.[2011-03] as varchar(20)),    cast(a.[2011-04] as varchar(20)),    cast(a.[2011-05] as varchar(20)),    cast(a.[2011-06] as varchar(20)),    cast(a.[2011-07] as varchar(20)),    cast(a.[2011-08] as varchar(20)),    cast(a.[2011-09] as varchar(20)),    cast(a.[2011-10] as varchar(20)),    cast(a.[2011-11] as varchar(20)),    cast(a.[2011-12] as varchar(20)),    cast(a.[以后年度合计] as varchar(20))  from #tempSaleDtl3 a  left join #product b on a.ProductGUID=b.ProductGUID  left join Project c on b.ProjectGUID=c.ProjectGUID GO--从Project表中加入项目数据DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份select * from (  select ProjectName as orderCode,ProjectGUID,ProjectName,    '--' AS '项目合计',    '--' AS '以前年度合计',    '--' AS '2011年合计',    '--' AS '2011-01',    '--' AS '2011-02',    '--' AS '2011-03',    '--' AS '2011-04',    '--' AS '2011-05',    '--' AS '2011-06',    '--' AS '2011-07',    '--' AS '2011-08',    '--' AS '2011-09',    '--' AS '2011-10',    '--' AS '2011-11',    '--' AS '2011-12',    '--' AS '以后年度合计'  from Project  where ProjectGUID=@ProjectGUID----项目1  8FA659C8-3DA9-4330-B277-9B517E67606D  项目1  union all--从产品表和Project表中加入合计行数据  select Project.ProjectName+'.'+a.ProductCode as orderCode,a.ProductGUID,a.ProductName,    '--' AS '项目合计',    '--' AS '以前年度合计',    '--' AS '2011年合计',    '--' AS '2011-01',    '--' AS '2011-02',    '--' AS '2011-03',    '--' AS '2011-04',    '--' AS '2011-05',    '--' AS '2011-06',    '--' AS '2011-07',    '--' AS '2011-08',    '--' AS '2011-09',    '--' AS '2011-10',    '--' AS '2011-11',    '--' AS '2011-12',    '--' AS '以后年度合计'  from #product a  left join Project on a.ProjectGUID=Project.ProjectGUIDunion ALL  --从产品表和Project表、#tempSaleDtl3中加入类型行数据  select c.ProjectName+'.'+b.ProductCode+'.'+a.typecode as orderCode,a.ProductGUID, a.[type],    cast(a.[项目合计] as varchar(20)),    cast(a.[以前年度合计] as varchar(20)),    cast(a.[2011年合计] as varchar(20)),    cast(a.[2011-01] as varchar(20)),    cast(a.[2011-02] as varchar(20)),    cast(a.[2011-03] as varchar(20)),    cast(a.[2011-04] as varchar(20)),    cast(a.[2011-05] as varchar(20)),    cast(a.[2011-06] as varchar(20)),    cast(a.[2011-07] as varchar(20)),    cast(a.[2011-08] as varchar(20)),    cast(a.[2011-09] as varchar(20)),    cast(a.[2011-10] as varchar(20)),    cast(a.[2011-11] as varchar(20)),    cast(a.[2011-12] as varchar(20)),    cast(a.[以后年度合计] as varchar(20))  from #tempSaleDtl3 a  left join #product b on a.ProductGUID=b.ProductGUID  left join Project c on b.ProjectGUID=c.ProjectGUID) t1 order by orderCodeGOdrop table #productdrop table #TempAllSaleDtlDROP TABLE #TempSaleDtlDROP TABLE #tempSaleDtl2DROP TABLE #tempSaleDtl3DROP TABLE #ProductSaleAreaselect * from dbo.SaleDtlDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE--SET STATISTICS IO ON--SET STATISTICS TIME ONDECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'DECLARE @Year CHAR(4)SET @Year='2011'/*临时表说明 #product:用项目过滤后,将“合计”作为一个产品的集合 #TempAllSaleDtl:通过项目过滤后的销售明细,所有月的 #ProductSaleArea:各个产品的总面积,用于计算比例 #TempSaleDtl:通过日期过滤,且加工过后的销售明细,包括增加累积列,以前年度、以后年度、项目合计的记录 #tempSaleDtl2:列转行后的数据集 #tempSaleDtl3:行转列后的数据集*/select ProductGUID,ProductName,ProjectGUID,ProductCode into #product from(select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCode from Product where ProjectGUID=@ProjectGUID  union all  select '00000000-0000-0000-0000-000000000000','合计',@ProjectGUID,'00' as ProductCode) a--SELECT * FROM #product----查找项目所有产品的销售明细:#TempAllSaleDtlSELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtl FROM dbo.SaleDtl WHERE ProductGUID IN (  SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID)--SELECT * FROM #TempAllSaleDtl--ORDER BY ProductGUID,YearMonth--根据现有数据统计,向#TempAllSaleDtl添加总合计记录insert into #TempAllSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice)select '00000000-0000-0000-0000-000000000000',YearMonth,SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea)from #TempAllSaleDtlgroup by YearMonth--SELECT * FROM #TempAllSaleDtl--ORDER BY ProductGUID,YearMonth--查找某年的销售明细:#TempSaleDtlSELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,  SalePrice AS ljSaleArea,  SalePrice AS blSaleArea,  SalePrice AS ljSaleAmount INTO #TempSaleDtl FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Year--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--获取项目各个产品的总销售面积:#ProductSaleAreaSELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUID--SELECT * FROM #ProductSaleArea--ORDER BY ProductGUID--添加2011合计列的记录(本年度的各产品的所有面积、金额、均价总合计)insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,@Year+'-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempSaleDtl group by ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--以前年度列记录(本年度以前的各产品的所有面积、金额、均价总合计 操作与上一步类似)insert into #TempSaleDtl(  ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,@Year+'-00',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl where YearMonth=@Year+'-00'group by ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--以后年度列记录(本年度以后的各产品的所有面积、金额、均价总合计 操作与上一步类似)insert into #TempSaleDtl(  ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,'9999-12',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl where YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00'group by ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--项目合计列记录(各产品取所有的合计。与上面的区别在于没有添加 here YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00')insert into #TempSaleDtl(  ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)select ProductGUID,'9999-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0from #TempAllSaleDtl group by ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--更新销售明细TempSaleDtl的累积销售面积、累积销售面积比例,累积销售金额UPDATE #TempSaleDtl SET  ljSaleArea=b.sum_SaleArea,  ljSaleAmount=b.sum_SaleAmount,  blSaleArea=b.sum_SaleArea/c.all_SaleArea FROM #TempSaleDtl left JOIN (  SELECT n.ProductGUID,n.YearMonth,SUM(m.SaleArea) AS sum_SaleArea,SUM(m.SaleAmount) AS sum_SaleAmount  FROM #TempAllSaleDtl m  INNER JOIN #TempSaleDtl n ON m.YearMonth=n.YearMonth AND m.ProductGUID=n.ProductGUID  GROUP BY n.ProductGUID,n.YearMonth) b ON #TempSaleDtl.ProductGUID=b.ProductGUID AND #TempSaleDtl.YearMonth=b.YearMonth LEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--列转行,转换后的表只有 产品、统计类型、日期,值4列;(每个产品对应的0-12、13 月对应的值)SELECT * INTO #tempSaleDtl2 FROM (  SELECT ProductGUID,'销售套数' AS type,'01' AS typecode,YearMonth,MAX(SaleNum) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'销售面积' AS type,'02' AS typecode,YearMonth,MAX(SaleArea) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'销售均价' AS type,'03' AS typecode,YearMonth,MAX(SalePrice) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'销售金额' AS type,'04' AS typecode,YearMonth,MAX(SaleAmount) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'累计销售面积' AS type, '05' AS typecode,YearMonth,SUM(ljSaleArea) FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'累计销售面积比例' AS type, '06' AS typecode,YearMonth,SUM(blSaleArea) FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'累计销售金额' AS type, '07' AS typecode,YearMonth,SUM(ljSaleAmount) FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth) t--SELECT * FROM #tempSaleDtl2--ORDER BY ProductGUID,yearmonth--行转列,按日期聚合SELECT ProductGUID,type,typecode,  MAX(CASE YearMonth WHEN '9999-13' THEN val ELSE 0 END) AS '项目合计',  MAX(CASE YearMonth WHEN @Year+'-00' THEN val ELSE 0 END) AS '以前年度合计',  MAX(CASE YearMonth WHEN @Year+'-13' THEN val ELSE 0 END) AS '2011年合计',  MAX(CASE YearMonth WHEN @Year+'-01' THEN val ELSE 0 END) AS '2011-01',  MAX(CASE YearMonth WHEN @Year+'-02' THEN val ELSE 0 END) AS '2011-02',  MAX(CASE YearMonth WHEN @Year+'-03' THEN val ELSE 0 END) AS '2011-03',  MAX(CASE YearMonth WHEN @Year+'-04' THEN val ELSE 0 END) AS '2011-04',  MAX(CASE YearMonth WHEN @Year+'-05' THEN val ELSE 0 END) AS '2011-05',  MAX(CASE YearMonth WHEN @Year+'-06' THEN val ELSE 0 END) AS '2011-06',  MAX(CASE YearMonth WHEN @Year+'-07' THEN val ELSE 0 END) AS '2011-07',  MAX(CASE YearMonth WHEN @Year+'-08' THEN val ELSE 0 END) AS '2011-08',  MAX(CASE YearMonth WHEN @Year+'-09' THEN val ELSE 0 END) AS '2011-09',  MAX(CASE YearMonth WHEN @Year+'-10' THEN val ELSE 0 END) AS '2011-10',  MAX(CASE YearMonth WHEN @Year+'-11' THEN val ELSE 0 END) AS '2011-11',  MAX(CASE YearMonth WHEN @Year+'-12' THEN val ELSE 0 END) AS '2011-12',  MAX(CASE YearMonth WHEN '9999-12' THEN val ELSE 0 END) AS '以后年度合计'into #tempSaleDtl3 FROM #tempSaleDtl2 GROUP BY ProductGUID,type,typecode ORDER BY ProductGUID,typecode--SELECT * FROM #tempSaleDtl3--ORDER BY ProductGUID,typecode--从Project表中加入项目数据select * from (  select ProjectName as orderCode,ProjectGUID,ProjectName,    '--' AS '项目合计',    '--' AS '以前年度合计',    '--' AS '2011年合计',    '--' AS '2011-01',    '--' AS '2011-02',    '--' AS '2011-03',    '--' AS '2011-04',    '--' AS '2011-05',    '--' AS '2011-06',    '--' AS '2011-07',    '--' AS '2011-08',    '--' AS '2011-09',    '--' AS '2011-10',    '--' AS '2011-11',    '--' AS '2011-12',   '--' AS '以后年度合计'  from Project  where ProjectGUID=@ProjectGUID----项目1  8FA659C8-3DA9-4330-B277-9B517E67606D  项目1   union all--从产品表和Project表中加入合计行数据  select Project.ProjectName+'.'+a.ProductCode as orderCode,a.ProductGUID,a.ProductName,    '--' AS '项目合计',    '--' AS '以前年度合计',    '--' AS '2011年合计',   '--' AS '2011-01',    '--' AS '2011-02',    '--' AS '2011-03',    '--' AS '2011-04',    '--' AS '2011-05',    '--' AS '2011-06',    '--' AS '2011-07',    '--' AS '2011-08',    '--' AS '2011-09',    '--' AS '2011-10',    '--' AS '2011-11',    '--' AS '2011-12',    '--' AS '以后年度合计'  from #product a  left join Project on a.ProjectGUID=Project.ProjectGUID  union ALL  --从产品表和Project表、#tempSaleDtl3中加入类型行数据  select c.ProjectName+'.'+b.ProductCode+'.'+a.typecode as orderCode,a.ProductGUID, a.[type],    cast(a.[项目合计] as varchar(20)),    cast(a.[以前年度合计] as varchar(20)),    cast(a.[2011年合计] as varchar(20)),   cast(a.[2011-01] as varchar(20)),    cast(a.[2011-02] as varchar(20)),    cast(a.[2011-03] as varchar(20)),    cast(a.[2011-04] as varchar(20)),    cast(a.[2011-05] as varchar(20)),    cast(a.[2011-06] as varchar(20)),    cast(a.[2011-07] as varchar(20)),    cast(a.[2011-08] as varchar(20)),    cast(a.[2011-09] as varchar(20)),    cast(a.[2011-10] as varchar(20)),    cast(a.[2011-11] as varchar(20)),    cast(a.[2011-12] as varchar(20)),    cast(a.[以后年度合计] as varchar(20))  from #tempSaleDtl3 a  left join #product b on a.ProductGUID=b.ProductGUID  left join Project c on b.ProjectGUID=c.ProjectGUID) t1 order by orderCodedrop table #productdrop table #TempAllSaleDtlDROP TABLE #TempSaleDtlDROP TABLE #tempSaleDtl2DROP TABLE #tempSaleDtl3DROP TABLE #ProductSaleAreaGO

原标题:sql server: sql script

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。