如果我们需要多步的操作，或者需要从不同的数据源表取数据组合，这时候可能就需要临时表来存储中间结果。
举一个简单的例子，我们有淘宝每天的销售额以及天猫每天的销售额分表在不同的表中存储。而我们的需求是将每个月淘宝以及天猫的销售额输出。
思路：
1. 先将淘宝和天猫按照月份的销售额数据获取到，然后再join
2. 要按照月份汇总销售额需要先将记录日期转化为月份得到新的表再按照月份group
CREATE PROCEDURE [dbo].[sp_GetMonthMetric]ASBEGIN -- 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,[email protected],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,[email protected],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,[email protected],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
上面这个代码是建立了一个存储过程来存储从三个数据源表汇总的最近六个月的月份KPI。对于Join中如果没有数据想默认设为0，可以用函数coalesce(col, defaultvalue)
代码示例中的“SELECT * INTO #TAB WHERE *”的代码即是建立了临时表，主要用#前缀表示，使用完毕后需要删除掉。