[教学] Sql Server 季节统计比较、季度统计分析的比较 指令
Sql Server 季节统计比较、季度统计分析的比较 Q1 Q2 Q3 Q4Quarterly Sales from Q1 to Q4 Comparison Select CommandCREATE TABLE #TEMP
(
Years int,
Quarters int,
Sales decimal(18, 2)
)
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2010', '3', '31276.09');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2010', '4', '23435.84');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '1', '26160.66');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '2', '36096.03');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '3', '46975.88');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2011', '4', '27410.06');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2012', '1', '28220.78');
INSERT INTO #TEMP(Years, Quarters, Sales) VALUES('2012', '2', '33409.81');
SELECT * FROM(
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
FROM #TEMP a
LEFT JOIN #TEMP b
ON a.Quarters -1 = b.Quarters and a.Years = b.Years
LEFT JOIN #TEMP c
ON a.Quarters = 1 and c.Quarters = 4 and a.Years -1 = c.Years
) d
ORDER BY d.Years DESC , d.Quarters DESC
DROP TABLE #TEMP
页:
[1]