大马资讯论坛 - 马来西亚中文资讯平台

 找回密码
 注册
搜索

[教学] Sql Server 季节统计比较、季度统计分析的比较 指令

[复制链接]
发表于 2013-9-24 21:25:14 | 显示全部楼层 |阅读模式
Sql Server 季节统计比较、季度统计分析的比较 Q1 Q2 Q3 Q4
Quarterly Sales from Q1 to Q4 Comparison Select Command
  1. CREATE TABLE #TEMP
  2. (
  3. Years int,
  4. Quarters int,
  5. Sales decimal(18, 2)
  6. )

  7. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2010', '3', '31276.09');
  8. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2010', '4', '23435.84');
  9. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '1', '26160.66');
  10. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '2', '36096.03');
  11. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '3', '46975.88');
  12. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '4', '27410.06');
  13. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2012', '1', '28220.78');
  14. INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2012', '2', '33409.81');

  15. SELECT * FROM(  
  16.         SELECT a.Years, a.Quarters, a.Sales AS Original, ISNULL(b.Years, c.Years) as PreYear,ISNULL(b.Quarters, c.Quarters) as PreQuarter, b.Sales AS Q1to3Sales, c.Sales AS Q4Sales, (a.Sales - ISNULL(b.Sales, c.Sales)) AS [Sales]   
  17.         FROM #TEMP a
  18.                 LEFT JOIN #TEMP b  
  19.                 ON a.Quarters -1 = b.Quarters and a.Years = b.Years
  20.                 LEFT JOIN #TEMP c  
  21.                 ON a.Quarters = 1 and c.Quarters = 4 and a.Years -1 = c.Years  
  22. ) d
  23. ORDER BY d.Years DESC , d.Quarters DESC

  24. DROP TABLE #TEMP
复制代码

手机版|大马资讯论坛  

GMT+8, 2024-3-29 04:08 , Processed in 0.251603 second(s), 13 queries , File On.

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表