sql server: sql script

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505

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-A8296FBF6A8A F637A079-E22B-4E50-87E9-000147B1B1F4    2013-04     7   700.0000    6000.0000   4200000.0000    2013    04  BEDF4CED-0C39-45EA-97BF-7C298C1D8A5B F637A079-E22B-4E50-87E9-000147B1B1F4    2008-11     9   900.0000    5000.0000   4500000.0000    2008    11  63DE92BA-4C08-4FF1-9A68-27CD4293084A F637A079-E22B-4E50-87E9-000147B1B1F4    2014-08     12  1200.0000   7000.0000   8400000.0000    2014    08  BC874228-B13F-4E49-9560-3CD39FF2B8A9 F637A079-E22B-4E50-87E9-000147B1B1F4    2011-05     12  12000.0000  7000.0000   84000000.0000   2011    05  164FA80F-3ECF-4E82-86A1-A7F832867028 F637A079-E22B-4E50-87E9-000147B1B1F4    2011-04     7   700.0000    6000.0000   4200000.0000    2011    04  7EC32C1E-F34B-4BF1-9BA4-5205C2C322F0 F637A079-E22B-4E50-87E9-000147B1B1F4    2010-05     12  12000.0000  7000.0000   84000000.0000   2010    05  278D72A4-F340-4DB9-8083-7BD277EB7C9E F637A079-E22B-4E50-87E9-000147B1B1F4    2013-11     9   900.0000    5000.0000   4500000.0000    2013    11  E70EF3D1-A92C-45C1-A7D3-110A57C93CF0 F637A079-E22B-4E50-87E9-000147B1B1F4    2015-08     12  1200.0000   7000.0000   8400000.0000    2015    08  26D1A828-26EE-4B9E-BEEF-A6F0A74CDE9E F637A079-E22B-4E50-87E9-000147B1B1F4    2007-02     8   800.0000    3500.0000   2800000.0000    2007    02  FD59AF4A-447D-4860-B2C2-10B0FD8C7531 F637A079-E22B-4E50-87E9-000147B1B1F4    2006-03     5   500.0000    5000.0000   2500000.0000    2006    03  9288807A-C53A-44FA-B9AF-A69E0BF1C4CE F637A079-E22B-4E50-87E9-000147B1B1F4    2006-02     8   800.0000    3500.0000   2800000.0000    2006    02  CF001CD9-BC4D-4FB8-B39E-0FE41D2468A8 F637A079-E22B-4E50-87E9-000147B1B1F4    2011-08     12  1200.0000   7000.0000   8400000.0000    2011    08  608070DC-53F0-46E0-B5F8-102106786073 F637A079-E22B-4E50-87E9-000147B1B1F4    2012-04     7   700.0000    6000.0000   4200000.0000    2012    04  9869C12C-0896-4C17-B575-3A2C52CBCE13 F637A079-E22B-4E50-87E9-000147B1B1F4    2014-10     3   300.0000    5000.0000   1500000.0000    2014    10  705F790B-23F3-4C9D-A9DA-A4B5799D1D56 F637A079-E22B-4E50-87E9-000147B1B1F4    2007-01     10  1000.0000   4000.0000   4000000.0000    2007    01  050DA0EE-8862-4F0F-92FE-A521208F09C3 F637A079-E22B-4E50-87E9-000147B1B1F4    2009-12     6   600.0000    5000.0000   3000000.0000    2009    12  6E0903D3-204E-4E3E-A737-39FCF7B434E8 F637A079-E22B-4E50-87E9-000147B1B1F4    2010-08     12  1200.0000   7000.0000   8400000.0000    2010    08  22A0FC81-77C1-4B37-9DA8-24166AF7A490 F637A079-E22B-4E50-87E9-000147B1B1F4    2011-07     8   800.0000    8000.0000   6400000.0000    2011    07  DDBDE42E-4986-44AC-BE77-8EC381356820 F637A079-E22B-4E50-87E9-000147B1B1F4    2014-11     9   900.0000    5000.0000   4500000.0000    2014    11  6A7B23A3-C48B-4519-986D-3901CCFA50AE F637A079-E22B-4E50-87E9-000147B1B1F4    2010-04     7   700.0000    6000.0000   4200000.0000    2010    04  1BA93EC8-CB89-4DA5-B27C-239A43099214 F637A079-E22B-4E50-87E9-000147B1B1F4    2009-04     7   700.0000    6000.0000   4200000.0000    2009    04  */         /* 临时表说明#product：用项目过滤后，将“合计”作为一个产品的集合#TempAllSaleDtl：通过项目过滤后的销售明细，所有月的#ProductSaleArea：各个产品的总面积，用于计算比例#TempSaleDtl：通过日期过滤，且加工过后的销售明细，包括增加累积列，以前年度、以后年度、项目合计的记录#tempSaleDtl2：列转行后的数据集#tempSaleDtl3：行转列后的数据集 */ DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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) a GO --查找该项目的所有产品的销售明细放进临时表#TempAllSaleDtl，以作备用： DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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 YearMonth go --从临时表#TempAllSaleDtl 中查找指定年的销售明细放进临时表#TempSaleDtl ，注意 这个时候就已经包含了 “合计”产品00的数据： --查找某年的销售明细：#TempSaleDtl DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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)=@Year go --从临时表#TempAllSaleDtl 中查找指定年的销售明细放进临时表#TempSaleDtl ，注意 这个时候就已经包含了 “合计”产品00的数据： --查找某年的销售明细：#TempSaleDtl DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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)=@Year go   --从#TempAllSaleDtl 中统计项目各个产品的总销售面积放入表：#ProductSaleArea，主要用作计算 项目累计销售面积比例 --获取项目各个产品的总销售面积：#ProductSaleArea SELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUID go --从表#TempSaleDtl 统计当前年度合计列，各产品的所有面积、金额、均价总合计 放入表#TempSaleDtl，注意这里 SUM(SaleAmount)/SUM(SaleArea) 计算销售单价： --添加2011合计列的记录(本年度的各产品的所有面积、金额、均价总合计) DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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 UNIQUEIDENTIFIER SET @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 UNIQUEIDENTIFIER SET @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 UNIQUEIDENTIFIER SET @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,typecode GO   select * from #tempSaleDtl3 GO   --从Project表中加入项目数据 DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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=@ProjectGUID GO   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 GO   --从产品表和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 UNIQUEIDENTIFIER SET @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.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 orderCode GO   drop table #product drop table #TempAllSaleDtl DROP TABLE #TempSaleDtl DROP TABLE #tempSaleDtl2 DROP TABLE #tempSaleDtl3 DROP TABLE #ProductSaleArea   select * from dbo.SaleDtl     DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE --SET STATISTICS IO ON --SET STATISTICS TIME ON DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @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 ----查找项目所有产品的销售明细：#TempAllSaleDtl SELECT 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 --查找某年的销售明细：#TempSaleDtl SELECT 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 --获取项目各个产品的总销售面积：#ProductSaleArea SELECT 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 orderCode   drop table #product drop table #TempAllSaleDtl DROP TABLE #TempSaleDtl DROP TABLE #tempSaleDtl2 DROP TABLE #tempSaleDtl3 DROP TABLE #ProductSaleArea GO

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123

/*开盘调价报告-本次开盘产品列表*/ ALTER  PROC usp_s_KpTjReport_KpProduct     (       @PlanGUID UNIQUEIDENTIFIER ,  --开盘调价方案GUID       @ProjGUID UNIQUEIDENTIFIER    --所属项目GUID     ) AS     SET NOCOUNT ON;     -------------------------------------------------------------------------------------------------------------------------------------------     --1. 查询最新审核项目分解版本     -------------------------------------------------------------------------------------------------------------------------------------------     DECLARE @ProjAnalysisGUID UNIQUEIDENTIFIER       SELECT TOP 1 @ProjAnalysisGUID=ProjAnalysisGUID     FROM dbo.s_ProjAnalysis     WHERE  ProjGUID=@ProjGUID AND State='已审核'     ORDER BY ShDate DESC           --项目名称(用于分区全名称匹配)     DECLARE @ProjName VARCHAR(400)           SELECT @ProjName=ProjName     FROM dbo.p_Project     WHERE ProjGUID=@ProjGUID           --SELECT @ProjAnalysisGUID     -------------------------------------------------------------------------------------------------------------------------------------------     --2. 查询出要求格式的分区和业态名称，以及列表计算需要的数据     -------------------------------------------------------------------------------------------------------------------------------------------     SELECT A.RoomGUID,         [ProductCode]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                         MAX(D.Area2ProductTypeCode) ELSE                         MAX(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductTypeCode END )                         END ,         [AreaName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                         MAX(ISNULL(D3.Area2ProductName+'-','')+ISNULL(D2.Area2ProductName+'-','')+D1.Area2ProductName) ELSE                         MAX(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN ISNULL(D3.Area2ProductName+'-','')+ISNULL(D2.Area2ProductName+'-','')+D1.Area2ProductName END)                         END,         [ProductName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                         MAX(D.Area2ProductName) ELSE                         MAX(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductName END )                         END ,         [BldArea]=MAX(A.BldArea),         [ToTal]=MAX(A.ToTal),         [ZhDiscntRate]=CAST(MAX(F.ZhDiscntRate)/100 AS DECIMAL(5,3)) --综合折扣率保存为1-100     INTO #A     FROM dbo.s_TjResult A         INNER JOIN dbo.p_room B ON B.RoomGUID=A.RoomGUID         INNER JOIN dbo.p_BuildProductType C ON C.BProductTypeCode=B.BProductTypeCode         INNER JOIN dbo.s_Area2Product D ON D.ProductTypeGUID=C.BuildProductTypeGUID AND D.ProjAnalysisGUID=@ProjAnalysisGUID--试算房间所属产品 --可能有多条         LEFT JOIN dbo.s_Area2Product D1 ON D1.Area2ProductTypeCode=D.ParentCode AND D1.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区         LEFT JOIN dbo.s_Area2Product D2 ON D2.Area2ProductTypeCode=D1.ParentCode AND D2.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区         LEFT JOIN dbo.s_Area2Product D3 ON D3.Area2ProductTypeCode=D2.ParentCode AND D3.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区上级分区         INNER JOIN dbo.p_Building E ON E.BldGUID=A.BldGUID--试算房间楼栋         INNER JOIN dbo.s_TjPlan F ON F.PlanGUID=@PlanGUID--开盘调价计划         LEFT JOIN dbo.p_Building G ON G.IsBld=0 AND G.ParentCode+'.'+G.BldCode=E.ParentCode AND B.ProjGUID=@ProjGUID--试算房间楼栋所属区域     WHERE A.PlanGUID=@PlanGUID     GROUP BY A.RoomGUID     HAVING COUNT(A.RoomGUID)=1 OR COUNT(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN 1 END)=1           --SELECT * FROM #A     -------------------------------------------------------------------------------------------------------------------------------------------     --3. 按业态计算指标     -------------------------------------------------------------------------------------------------------------------------------------------     SELECT  AreaName=MAX(A.AreaName)             ,ProductName=MAX(A.ProductName)             ,RoomCount=COUNT(1)             ,BldArea=CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0))--面积             ,ZQJJ=  CASE WHEN CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0))=0 THEN 0 ELSE                         CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))                         / CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0)) END --折前均价（元/平米）             ,ZQTotal=CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))--折前总价（万元）             ,ZSJJ=  CASE WHEN CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0))=0 THEN 0 ELSE                         CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))                         / CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0)) END                     * MAX(A.ZhDiscntRate)--折实均价（元/平米）             ,ZSTotal=   CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))                         *MAX(A.ZhDiscntRate)--折实总价（万元）             ,Remarks=MAX('综合折扣率为：'+CAST(CAST(A.ZhDiscntRate*100 AS DECIMAL(5,1))AS VARCHAR(5))+'%')--备注     INTO    #B     FROM    #A A     GROUP   BY A.ProductCode     -------------------------------------------------------------------------------------------------------------------------------------------     --4. 联合查询出列表展现结果     -------------------------------------------------------------------------------------------------------------------------------------------     SELECT  AreaName=A.AreaName ,--分区             ProductName ,--业态             RoomCount ,--套数             BldArea=CAST(BldArea AS DECIMAL(18,0)),--面积             ZQJJ =CAST(ZQJJ AS DECIMAL(18,0)),--折前均价             ZQTotal=CAST(ZQTotal AS DECIMAL(18,0)) ,--折前总价             ZSJJ =CAST(ZSJJ AS DECIMAL(18,0)),--折实均价             ZSTotal =CAST(ZSTotal AS DECIMAL(18,0)),--折实总价             Remarks,--备注             FqRowspan=CASE WHEN A.AreaName IS NULL THEN 1 ELSE B.FqRowspan END --当前分区下产品个数（用于Rowspan）     FROM #B A         INNER JOIN (             SELECT AreaName,COUNT(1) AS FqRowspan             FROM #B             GROUP BY AreaName         ) B ON B.AreaName IS NULL OR B.AreaName=A.AreaName--加入统计每个分区的产品个数     UNION ALL     SELECT  '合计',             NULL,             SUM(RoomCount) ,             SUM(CAST(BldArea AS DECIMAL(18,0))),             NULL,             SUM(CAST(ZQTotal AS DECIMAL(18,0))),             NULL,             SUM(CAST(ZSTotal AS DECIMAL(18,0))),             MAX(Remarks),             1     FROM #B     ORDER BY AreaName ASC,ProductName DESC           --SELECT * FROM #A ORDER BY RoomGUID     -------------------------------------------------------------------------------------------------------------------------------------------     --5. 删除临时表     -------------------------------------------------------------------------------------------------------------------------------------------     DROP TABLE #A     DROP TABLE #B   GO

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297

/*开盘调价方案审批-工作流域取数*/ ALTER  PROC usp_s_KpTjReport_WF     (       @PlanGUID UNIQUEIDENTIFIER    --开盘调价方案GUID     ) AS     SET NOCOUNT ON;     DECLARE @JcwjDiff DECIMAL(18,0)--累计实现业态均价与决策文件均价差额(当前未开票或未匹配业态则0，若任一业态均价小于0则为-1，否则为1)     DECLARE @ProjGUID UNIQUEIDENTIFIER--当前项目GUID     DECLARE @ProjAnalysisGUID UNIQUEIDENTIFIER--当前最新审核项目分解版本GUID     DECLARE @PlanType VARCHAR(20)--当前方案类型     DECLARE @TjDiff DECIMAL(18,0)--调整前后总价差额合计(万元)     DECLARE @TjTotal DECIMAL(18,0)--调价房间总价(万元)     DECLARE @TjCount BIGINT--调价房间套数           -------------------------------------------------------------------------------------------------------------------------------------------     --1. 查询累计实现业态均价与决策文件均价差额     --1.1 获取当前项目和最新审核项目分解版本     -------------------------------------------------------------------------------------------------------------------------------------------     SET @JcwjDiff=0 --默认值           --项目GUID及报告类型     SELECT @ProjGUID=ProjGUID,@PlanType=PlanType     FROM dbo.s_TjPlan     WHERE PlanGUID=@PlanGUID           --最新审核项目分解版本     SELECT TOP 1 @ProjAnalysisGUID=ProjAnalysisGUID     FROM dbo.s_ProjAnalysis     WHERE  ProjGUID=@ProjGUID AND State='已审核'     ORDER BY ShDate DESC           --项目名称(用于分区全名称匹配)     DECLARE @ProjName VARCHAR(400)           SELECT @ProjName=ProjName     FROM dbo.p_Project     WHERE ProjGUID=@ProjGUID     --SELECT @ProjGUID AS 项目,@PlanType AS 类型,@ProjAnalysisGUID AS 项目分解           IF( @PlanType='调价报告')     BEGIN         SELECT A.RoomGUID,--A.bldGUID,COUNT(A.RoomGUID) AS CRoomGUID,COUNT(A.BldGUID) AS CBld,COUNT(CASE WHEN C1.Area2ProductName=A.FqName THEN 1 END) AS C,                 [ProductCode]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D.Area2ProductTypeCode)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductTypeCode END)                                 END ,                 [AreaName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D1.Area2ProductName)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D1.Area2ProductName END)                                 END,                 [ProductName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D.Area2ProductName)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductName END)                                 END ,                 [RgTotal]=MAX(E.CjTotal),--元                 [RgBldArea]=MAX(E.BldArea),                 [HtTotal]=MAX(F.HtTotal),--元                 [HtBldArea]=MAX(F.BldArea),                 [AttachRoomArea]=MAX(O.BldArea),--认购合同附属房产的面积                 [KpDate]=MAX(H.KpDate),                 [RoomState]=MAX(B.Status),                 [TjTotal]=MAX(A.Total),--元                 [RoomBzTotal]=MAX(B.Total),--元                 [RoomBldArea]=MAX(B.BldArea),                 [XmFjPrice]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D.SalePrice)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SalePrice END)                                 END ,--元                 [BldSalePrice]=MAX(M.SalePrice) ,--元                 [BldSaleArea]=MAX(M.SaleArea),                 [JcwjSaleTotal]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D.SalePrice)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SaleTotal END)                                 END ,--万元                 [JcwjSaleArea]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D.SaleArea)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SaleArea END)                                 END ,                 [JcwjSalePrice]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                                     MAX(D.SalePrice)                                 ELSE                                     MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SalePrice END)                                 END --元         INTO #A         FROM dbo.s_TjResult A             --对应业态分区             INNER JOIN dbo.p_room B ON B.RoomGUID=A.RoomGUID             INNER JOIN dbo.p_BuildProductType C ON C.BProductTypeCode=B.BProductTypeCode             INNER JOIN dbo.s_Area2Product D ON D.ProductTypeGUID=C.BuildProductTypeGUID AND D.ProjAnalysisGUID=@ProjAnalysisGUID --可能有多条             LEFT JOIN dbo.s_Area2Product D1 ON D1.Area2ProductTypeCode=D.ParentCode AND D1.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区             LEFT JOIN dbo.s_Area2Product D2 ON D2.Area2ProductTypeCode=D1.ParentCode AND D2.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区             LEFT JOIN dbo.s_Area2Product D3 ON D3.Area2ProductTypeCode=D2.ParentCode AND D3.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区上级分区             ----对应合同订单             LEFT JOIN dbo.s_order E ON E.RoomGUID =A.RoomGUID AND E.Status='激活'             LEFT JOIN dbo.s_contract F ON F.RoomGUID =A.RoomGUID AND F.Status='激活'             --合同订单附属房产（统计销售面积）             LEFT JOIN dbo.s_OCAttachRoom O ON O.RoomGUID=A.RoomGUID AND O.SaleGUID IN(E.OrderGUID,F.ContractGUID)--一个房间不可能同时被认购和签约             ----对应开盘计划（取开盘日期）             LEFT JOIN dbo.s_TjResult G ON G.RoomGUID=A.RoomGUID AND G.PlanGUID IN(SELECT PlanGUID FROM s_tjPlan WHERE PlanType='开盘报告' AND ProjGUID=@ProjGUID)--对应开盘计划的试算结果             LEFT JOIN dbo.s_TjPlan H ON H.PlanGUID=G.PlanGUID--对应的开盘计划             --对应开盘或调价试算结果（当房间做多个调价报告时，取最新制定的方案试算记录）             LEFT JOIN dbo.s_TjResult K ON K.RoomGUID=A.RoomGUID AND K.PlanGUID IN (                                                                   SELECT TOP 1 X.PlanGUID                                                                   FROM      s_tjPlan X                                                                             INNER JOIN ( SELECT PlanGUID                                                                                          FROM s_TjResult                                                                                          WHERE RoomGUID = A.RoomGUID                                                                                        ) L ON L.PlanGUID = X.PlanGUID                                                                   WHERE X.ShDate IS NOT NULL --要求已审核                                                                   ORDER BY  X.ZdDate DESC )             INNER JOIN dbo.p_Building M ON M.IsBld=1 AND M.BldGUID=A.BldGUID--房间所属楼栋             LEFT JOIN dbo.p_Building N ON N.IsBld=0 AND N.ParentCode+'.'+N.BldCode=M.ParentCode AND N.ProjGUID=@ProjGUID--楼栋所属区域         WHERE A.PlanGUID=@PlanGUID         GROUP BY A.RoomGUID         HAVING COUNT(A.RoomGUID)=1 OR COUNT(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN 1 END)=1           --SELECT * FROM #A ORDER BY [ProductCode] DESC                   IF(EXISTS (SELECT 1 FROM #A))--当未匹配业态，则决策文件均价差额为0         BEGIN             -------------------------------------------------------------------------------------------------------------------------------------------             --1.3 按业态计算指标,获得预计整体可实现折实均价与决策文件差额             -------------------------------------------------------------------------------------------------------------------------------------------             SELECT A.ProductCode                 --预计整体可实现与决策文件差额                 ,[Diff]=                         --'折实均价(元/平米)'                         CASE WHEN                         (                             ISNULL(SUM(CASE WHEN A.RoomState IN ('认购','签约') THEN ISNULL(A.RgBldArea,0)+ISNULL(A.HtBldArea,0)+ISNULL(A.[AttachRoomArea],0) END),0)                             +ISNULL( SUM(CASE WHEN DATEDIFF(DAY,GETDATE(), A.[KpDate])<=0 AND A.RoomState NOT IN ('认购','签约') THEN A.[RoomBldArea] END),0)                             +ISNULL(                                 SUM(                                 CASE WHEN                                     ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0                                 THEN                                     --如果所有楼栋都生成房间，则统计房间建筑面积之和                                     --如果部分楼栋没有生成房间，则显示生成房间楼栋建筑面积之和+楼栋预计销售面积                                     CASE WHEN A.RoomGUID IS NULL THEN A.[BldSaleArea]                                         ELSE A.[RoomBldArea] END                                  END )                             ,0)                         )=0 THEN 0 ELSE                         (                             ISNULL(SUM(CASE WHEN A.RoomState IN ('认购','签约') THEN ISNULL(A.RgTotal,0)+ISNULL(A.HtTotal,0) END),0)                             +ISNULL(SUM(CASE WHEN DATEDIFF(DAY,GETDATE(), A.[KpDate])<=0 AND A.RoomState NOT IN ('认购','签约') THEN A.[RoomBzTotal] END),0)                             +                                 (ISNULL(SUM(CASE WHEN ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0 AND ISNULL(A.[TjTotal],0)<>0 THEN A.[TjTotal] END),0)--未到推盘日期已经定价房间  的房间总价                                 +ISNULL(SUM(CASE WHEN ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0 AND ISNULL(A.[TjTotal],0)=0 AND ISNULL(A.[RoomBzTotal],0)<>0 THEN A.XmFjPrice*A.[RoomBldArea] END),0)--未到推盘日期未定价房间  的房间总价                                 +ISNULL(SUM(CASE WHEN ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0 AND NOT(ISNULL(A.[TjTotal],0)<>0) AND NOT(ISNULL(A.[TjTotal],0)=0 AND ISNULL(A.[RoomBzTotal],0)<>0) THEN A.[BldSalePrice]*A.[BldSaleArea] END),0)--未到推盘日期未定价的楼栋房间                                 )                         )                         /(                             ISNULL(SUM(CASE WHEN A.RoomState IN ('认购','签约') THEN ISNULL(A.RgBldArea,0)+ISNULL(A.HtBldArea,0)+ISNULL(A.[AttachRoomArea],0) END),0)                             +ISNULL( SUM(CASE WHEN DATEDIFF(DAY,GETDATE(), A.[KpDate])<=0 AND A.RoomState NOT IN ('认购','签约') THEN A.[RoomBldArea] END),0)                             +ISNULL(                                 SUM(                                 CASE WHEN                                     ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0                                 THEN                                     --如果所有楼栋都生成房间，则统计房间建筑面积之和                                     --如果部分楼栋没有生成房间，则显示生成房间楼栋建筑面积之和+楼栋预计销售面积                                     CASE WHEN A.RoomGUID IS NULL THEN A.[BldSaleArea]                                         ELSE A.[RoomBldArea] END                                  END )                             ,0)                         ) END                         -ISNULL(MAX(A.[JcwjSalePrice]),0)             INTO #A1             FROM #A A             GROUP BY A.ProductCode             -------------------------------------------------------------------------------------------------------------------------------------------             --1.4 计算累计实现业态均价与决策文件均价差额             --1.5 当其中有一个差额小于0，则均价差额返回-1             --1.6 当所有差额都大于0，则均价差额返回1             --1.7 删除临时表             -------------------------------------------------------------------------------------------------------------------------------------------             --SELECT 1,* FROM #A1                           IF(EXISTS(SELECT 1 FROM #A1 WHERE Diff<0))                 SET @JcwjDiff=-1             ELSE                 SET @JcwjDiff=1                           DROP TABLE #A1         END ;                   DROP TABLE #A     END ;     --SELECT @JcwjDiff     -------------------------------------------------------------------------------------------------------------------------------------------     --2. 查询调整前后总价差额合计     --2.1 最近一次执行的开盘或调价方案     --2.1 如果是“开盘报告”或之前没有报告则不计算     -------------------------------------------------------------------------------------------------------------------------------------------         --获取本次调价的审核日期（用于查看历史记录时筛选）         DECLARE @ShDate DATETIME                   SELECT @ShDate=ShDate         FROM dbo.s_TjPlan         WHERE PlanGUID=@PlanGUID           -------------------------------------------------------------------------------------------------------------------------------------------         --2.2 查询比较的基本信息         -------------------------------------------------------------------------------------------------------------------------------------------         SELECT A.RoomGUID,             [ProductCode]=CASE WHEN COUNT(A.RoomGUID)=1 THEN                             MAX(E.Area2ProductTypeCode) ELSE                             MAX(CASE WHEN I.BldFullName=@ProjName+ISNULL('-'+E3.Area2ProductName,'')+ISNULL('-'+E2.Area2ProductName,'')+ISNULL('-'+E1.Area2ProductName,'') THEN E.Area2ProductTypeCode END )                             END ,             [OldSaleTotal]=MAX(B.Total),             [NewSaleTotal]=MAX(A.Total),             [OldBldArea]=MAX(B.BldArea),             [NewBldArea]=MAX(A.BldArea),             [OldZhDiscntRate]=CAST(MAX(H.ZhDiscntRate)/100 AS DECIMAL(5,3)), --综合折扣率保存为1-100             [NewZhDiscntRate]=CAST(MAX(G.ZhDiscntRate)/100 AS DECIMAL(5,3)) --综合折扣率保存为1-100         INTO #B         --SELECT C.RoomGUID,D.BuildProductTypeGUID,A.PlanGUID,B.PlanGUID         FROM dbo.s_TjResult A             INNER JOIN dbo.p_Room C ON C.RoomGUID=A.RoomGUID             INNER JOIN dbo.s_TjResult B ON b.RoomGUID=A.RoomGUID --房间对应的之前最近审核过的一次开盘调价方案试算                        AND B.PlanGUID IN (                                           SELECT TOP 1 X.PlanGUID                                           FROM      s_tjPlan X                                                     INNER JOIN ( SELECT PlanGUID                                                                  FROM s_TjResult                                                                  WHERE PlanGUID<>@PlanGUID AND RoomGUID = A.RoomGUID                                                                ) Y ON Y.PlanGUID = X.PlanGUID                                           WHERE     x.ShDate<ISNULL(@ShDate,GETDATE())                                           ORDER BY  X.ShDate DESC )             INNER JOIN dbo.p_BuildProductType D ON D.BProductTypeCode=C.BProductTypeCode             INNER JOIN dbo.s_Area2Product E ON E.ProductTypeGUID=D.BuildProductTypeGUID AND E.ProjAnalysisGUID=@ProjAnalysisGUID---可能有多条             LEFT JOIN dbo.s_Area2Product E1 ON E1.Area2ProductTypeCode=E.ParentCode AND E1.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区             LEFT JOIN dbo.s_Area2Product E2 ON E2.Area2ProductTypeCode=E1.ParentCode AND E2.ProjAnalysisGUID=@ProjAnalysisGUID----产品所属分区上级分区             LEFT JOIN dbo.s_Area2Product E3 ON E3.Area2ProductTypeCode=E2.ParentCode AND E3.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区上级分区             INNER JOIN dbo.p_Building F ON F.BldGUID=A.BldGUID--试算房间楼栋             INNER JOIN dbo.s_TjPlan G ON G.PlanGUID=A.PlanGUID             INNER JOIN dbo.s_TjPlan H ON H.PlanGUID=B.PlanGUID             LEFT JOIN dbo.p_Building I ON I.IsBld=0 AND I.ParentCode+'.'+I.BldCode=F.ParentCode AND I.ProjGUID=@ProjGUID--楼栋所属区域         WHERE A.PlanGUID=@PlanGUID         GROUP BY A.RoomGUID         HAVING COUNT(A.RoomGUID)=1 OR COUNT(CASE WHEN I.BldFullName=@ProjName+ISNULL('-'+E3.Area2ProductName,'')+ISNULL('-'+E2.Area2ProductName,'')+ISNULL('-'+E1.Area2ProductName,'') THEN 1 END)=1                       -------------------------------------------------------------------------------------------------------------------------------------------         --2.3 按业态统计总价差额         -------------------------------------------------------------------------------------------------------------------------------------------         SELECT    [Diff] = CAST(ISNULL(SUM(A.NewSaleTotal*A.NewZhDiscntRate),0)/10000 - ISNULL(SUM(A.OldSaleTotal*A.OldZhDiscntRate),0)/10000 AS DECIMAL(18, 0))                 --差额=折实总价（调整后）-折实总价（调整前）         INTO      #B1         FROM      #B A         GROUP BY  A.ProductCode         -------------------------------------------------------------------------------------------------------------------------------------------         --2.3 计算所有总价差额         --2.4 删除临时表         -------------------------------------------------------------------------------------------------------------------------------------------         SELECT @TjDiff=SUM([Diff]) FROM #B1           DROP TABLE #B         DROP TABLE #B1             -------------------------------------------------------------------------------------------------------------------------------------------     --3. 输出开盘调价审批工作流相关域数据     -------------------------------------------------------------------------------------------------------------------------------------------     SELECT  @TjTotal = CAST(SUM(Total)/10000 AS DECIMAL(18, 0)) ,             @TjCount = COUNT(1)     FROM    dbo.s_TjResult     WHERE   PlanGUID = @PlanGUID     --     SELECT  A.PlanGUID ,             A.SxDate ,             B.ProjName ,             PlanName ,             ZdDate ,             PlanType ,             ZhDiscntRate=CAST(ZhDiscntRate AS DECIMAL(5,1)) ,             KpDate ,             TlDiscntRate=CAST(TlDiscntRate AS DECIMAL(5,1)) ,             --             [TjTotal] =@TjTotal ,--调价房间总价             [TjCount] = @TjCount ,--调价房间套数             [TjDiff] = ISNULL(CASE WHEN A.PlanType='开盘报告' THEN @TjTotal                                    ELSE @TjDiff                               END, 0) ,--调整前后总价差额合计             [JcwjDiff] = @JcwjDiff --累计实现业态均价与决策文件均价差额     FROM    s_TjPlan A             LEFT JOIN dbo.p_Project B ON B.ProjGUID = A.PRojGUID     WHERE   A.PlanGUID = @PlanGUID   GO