MS SQL借助临时表处理获取复杂结果

如果我们需要多步的操作，或者需要从不同的数据源表取数据组合，这时候可能就需要临时表来存储中间结果。

举一个简单的例子，我们有淘宝每天的销售额以及天猫每天的销售额分表在不同的表中存储。而我们的需求是将每个月淘宝以及天猫的销售额输出。

思路：

1. 先将淘宝和天猫按照月份的销售额数据获取到，然后再join

2. 要按照月份汇总销售额需要先将记录日期转化为月份得到新的表再按照月份group

CREATE PROCEDURE [dbo].[sp_GetMonthMetric]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON; 

	DECLARE @Day DateTime
	SET @Day=dateadd(month,0,convert(varchar(10),getdate(),120))

	DECLARE @Months int
	SET @Months=6

	-- Text Ads
	SELECT 
			LEFT(convert(varchar(10),Date,120),7) AS MON,
			Revenue,
			SRPV
	INTO #TA
	FROM [dbo].[Taobao_Daily]
	WHERE 
			LEFT(convert(varchar(10),Date,120),7) >= LEFT(convert(varchar(10),dateadd(month,-@Months,convert(varchar(10),@Day,120)),120),7)
		AND	LEFT(convert(varchar(10),Date,120),7) < LEFT(convert(varchar(10),@Day,120),7);

	SELECT 
			MON,
			SUM(Revenue) AS Revenue_TA,
			SUM(SRPV) AS SRPV
	INTO #TA_MON
	FROM #TA
	GROUP BY MON;
	
	-- TM
    SELECT 
			LEFT(convert(varchar(10),Date,120),7) AS MON,
			TMRevenue
	INTO #TM
	FROM [dbo].[Tmall_Daily]
	WHERE 
			LEFT(convert(varchar(10),Date,120),7) >= LEFT(convert(varchar(10),dateadd(month,-@Months,convert(varchar(10),@Day,120)),120),7)
		AND	LEFT(convert(varchar(10),Date,120),7) < LEFT(convert(varchar(10),@Day,120),7);

	SELECT 
			MON,
			SUM(TMRevenue) AS Revenue_TM
	INTO #TM_MON
	FROM #TM
	GROUP BY MON;

	-- ALP
	SELECT 
			LEFT(convert(varchar(10),Date,120),7) AS MON,
			Revenue
	INTO #ALP
	FROM [dbo].[Alipay_Daily]
	WHERE 
			LEFT(convert(varchar(10),Date,120),7) >= LEFT(convert(varchar(10),dateadd(month,-@Months,convert(varchar(10),@Day,120)),120),7)
		AND	LEFT(convert(varchar(10),Date,120),7) < LEFT(convert(varchar(10),@Day,120),7);

    SELECT 
			MON,
			SUM(Revenue) AS Revenue_ALP
	INTO #ALP_MON
	FROM #ALP
	GROUP BY MON;

	SELECT 
			#TA_MON.MON AS [Month],
			SRPV,
			Revenue_TA,
			coalesce(Revenue_TM,0) AS Revenue_TM,
			coalesce(Revenue_ALP,0) AS Revenue_ALP,
			Revenue_TA+Revenue_TM+Revenue_ALP AS Revenue_Overall,
			(Revenue_TA+Revenue_TM+Revenue_ALP)*1000/SRPV AS RPM
	INTO #CUR
	FROM #TA_MON
	LEFT JOIN #TM_MON ON #TA_MON.MON = #TM_MON.MON
	LEFT JOIN #ALP_MON ON #TA_MON.MON = #ALP_MON.MON;

	SELECT 
			[Month],
			SRPV,
			Revenue_TA,
			Revenue_TM,
			Revenue_ALP,
			Revenue_TA+Revenue_TM+Revenue_ALP AS Revenue_Overall,
			(Revenue_TA+Revenue_TM+Revenue_ALP)*1000/SRPV AS RPM
	FROM #CUR;

	DROP TABLE #TA_MON;
	DROP TABLE #TM_MON;
	DROP TABLE #ALP_MON;
	DROP TABLE #TA;
	DROP TABLE #TM;
	DROP TABLE #ALP;
	DROP TABLE #CUR;

END

代码示例中的“SELECT * INTO #TAB WHERE *”的代码即是建立了临时表，主要用#前缀表示，使用完毕后需要删除掉。