在Sql 中可以创建“只在当前回话中有效”的临时表,就像函数中的临时变量一样。
假设有这样一个表
Name | CreateTime | Type1 | Type2 |
名称 | 创建时间 | 类型1 | 类型2 |
现在我们需要找出“创建时间是07年一季度之内的”记录,然后按照Type1、Type2两种方式统计每个月创建的记录数。一般情况下我们会用下面的Sql:
一月份、type1
SELECT ' 一月份 ' , ' Type1 ' , Count ( * ) from Table1
where createtime between ' 2007-1-1 ' and ' 2007-2-1 '
group by type1;
二月份、type1
SELECT ' 二月份 ' , ' Type1 ' , Count ( * ) from Table1
where createtime between ' 2007-2-1 ' and ' 2007-3-1 '
group by type1;
三月份、type1
SELECT ' 三月份 ' , ' Type1 ' , Count ( * ) from Table1
where createtime between ' 2007-3-1 ' and ' 2007-4-1 '
group by type1;
一月份、type2
SELECT ' 一月份 ' , ' Type2 ' , Count ( * ) from Table1
where createtime between ' 2007-1-1 ' and ' 2007-2-1 '
group by type2;
SELECT ' 一月份 ' , ' Type1 ' , Count ( * ) from Table1
where createtime between ' 2007-1-1 ' and ' 2007-2-1 '
group by type1;
二月份、type1
SELECT ' 二月份 ' , ' Type1 ' , Count ( * ) from Table1
where createtime between ' 2007-2-1 ' and ' 2007-3-1 '
group by type1;
三月份、type1
SELECT ' 三月份 ' , ' Type1 ' , Count ( * ) from Table1
where createtime between ' 2007-3-1 ' and ' 2007-4-1 '
group by type1;
一月份、type2
SELECT ' 一月份 ' , ' Type2 ' , Count ( * ) from Table1
where createtime between ' 2007-1-1 ' and ' 2007-2-1 '
group by type2;
………………
共需要六个类似的查询。
像上面的统计方法,每次都在整个表中查询然后group,假设整个表里面的数据有非常非常多,那这六次查询加起来可能就会影响系统性能。
像这种情况,很容易的我们会想到,把一季度内创建的所有记录保存在一个临时表中,然后在临时表中统计。于是,我们可以这样做:
/**/
/* 将一季度的所有数据查询出来,并保存到临时表 Temp中 */
SELECT * FROM Table1
INTO #Temp
Where CreateTime Between ' 2007-1-1 ' and ' 2007-4-1 ' ;
一月份、type1
SELECT '一月份', 'Type1', Count(*) from #Temp
where createtime between '2007-1-1' and '2007-2-1'
group by type1;
二月份、type1
SELECT '二月份', 'Type1', Count(*) from #Temp
where createtime between '2007-2-1' and '2007-3-1'
group by type1;
三月份、type1
SELECT '三月份', 'Type1', Count(*) from #Temp
where createtime between '2007-3-1' and '2007-4-1'
group by type1;
一月份、type2
SELECT '一月份', 'Type2', Count(*) from #Temp
where createtime between '2007-1-1' and '2007-2-1'
group by type2;
SELECT * FROM Table1
INTO #Temp
Where CreateTime Between ' 2007-1-1 ' and ' 2007-4-1 ' ;
一月份、type1
SELECT '一月份', 'Type1', Count(*) from #Temp
where createtime between '2007-1-1' and '2007-2-1'
group by type1;
二月份、type1
SELECT '二月份', 'Type1', Count(*) from #Temp
where createtime between '2007-2-1' and '2007-3-1'
group by type1;
三月份、type1
SELECT '三月份', 'Type1', Count(*) from #Temp
where createtime between '2007-3-1' and '2007-4-1'
group by type1;
一月份、type2
SELECT '一月份', 'Type2', Count(*) from #Temp
where createtime between '2007-1-1' and '2007-2-1'
group by type2;
其中表 #temp 就是自动创建的临时表。这样,将符合条件的记录都保存在临时表之后,再从临时表中去查询、group,sql 就会少做许多遍历,提高了性能。
临时表的创建方法有很多种,具体请参考sql 帮助。