项目开发笔记(临时视图)-1

电信话单查询模块开发心得(临时视图的使用)

   前个星期，在女友的关系介绍下，找到了项目，就是开发一个话单查询子系统，由于是电信级别的东西，数据量自然是大了，日数据量就有５００万左右，听了都是吓人的（具体我也不清楚，因为是负责人说的）．
　项目拿到了，碰到的首要问题就是：如何跨多表查询？
例如：２００６－５－１日的数据就存在 F_2005050２中
            ２００６－５－２日的数据就存在 F_2005050２中
　　　２００６－５－３日的数据就存在 F_2005050３中
依次类推．．．
　　　而，用户查询的时候，又需要从多个表中查询数据．例如：查２００６－５－１　到　２００６－５－１０的数据，就要跨１０个表．（真的是很麻烦）
经过２天的思索，我想出了以下的方案．用存储过程实现，通过传入　start_time (起始时间) end_time（结束时间），用循环查询先确定表是否存在，然后动态生成一个 select 语句．用 union all 把这些个select语句组合在一起．代码如下：
declare @sd datetime
declare @ed datetime
declare @td datetime
declare @NN int
declare @d int
declare @sql varchar(4000)
declare @T varchar(255)
set @sd=@start_time--CAST('2006-05-01 12:35:29.998' AS datetime)
set @ed=@end_time --CAST('2006-05-03 12:35:29.998' AS datetime)
--set @td=@sd
set @d=datediff(DD,@sd,@ed)
print @d
print @td

set @NN=0
set @sql=' '

DECLARE @FLAG  INT
SET @FLAG=0

-- 生成待查询的数据表 集合

while @NN<=@d
begin
  set @td=dateadd(DD,@NN,@sd)
  set @NN=@NN+1
  SET @T=DATEPART(YYYY,@td)
  if DATEPART(MM,@td)<10
     set @T=@T+'0'+cast(DATEPART(MM,@td) as char(1))
  else
     set @T=@T+CAST(DATEPART(MM,@td) AS CHAR(2))
  if DATEPART(DD,@td)<10
     set @T=@T+'0'+cast(DATEPART(DD,@td) as char(1))
  else
     set @T=@T+CAST(DATEPART(DD,@td) AS CHAR(2))
 SET @T='FACT_'+@T
 --PRINT @T   
 if exists (select * from sysobjects where id = object_id(@T) and OBJECTPROPERTY(id, 'IsUserTable') = 1)
 BEGIN
  --PRINT '-----'
  PRINT @T
  --PRINT '!----!'
  --考虑时间范围 小时/分
  IF @FLAG=0
  begin
     SET @SQL='SELECT '+@fileds+' FROM '+@T+' where start_time>='''+cast(@start_time as varchar(50))+''' and '+@query
     SET @FLAG=1
  end
  ELSE
  begin
    SET @SQL=@SQL+' UNION ALL SELECT '+@fileds+' FROM '+@T+' where '+@query
  end
 END
 
end

　　到此处，@SQL里，已经是多个表查询的语句了．再在外面套上一个 select 语句就可以查询．
　　如： @SQL='select * from ('+@SQL+')'就可以了
      最后exec(@SQL)就可以返回数据了．

不过，经过实际运用，该方法不太好，生成的sql 语句太长，而且是用不方便．于是我又想出了第２种办法，就是＂临时视图＂，为什么有＂临时表＂就不能有＂临时视图＂呢？
　　采用了该方法后，程序大大简化了．而且使用方便了，至于效率吗（机器效率）应该是低点．
具体思路是：
　　　程序运行时，先确定范围，然后根据范围自动创建一个视图，提供给后续查询用，查询完毕后，用drop把视图删除．
          具体如下
while @NN<=@d
begin
  set @td=dateadd(DD,@NN,@sd)
  set @NN=@NN+1
  SET @T=DATEPART(YYYY,@td)
  if DATEPART(MM,@td)<10
     set @T=@T+'0'+cast(DATEPART(MM,@td) as char(1))
  else
     set @T=@T+CAST(DATEPART(MM,@td) AS CHAR(2))
  if DATEPART(DD,@td)<10
     set @T=@T+'0'+cast(DATEPART(DD,@td) as char(1))
  else
     set @T=@T+CAST(DATEPART(DD,@td) AS CHAR(2))
 SET @T='FACT_'+@T
 
 --PRINT @T   
 if exists (select * from sysobjects where id = object_id(@T) and OBJECTPROPERTY(id, 'IsUserTable') = 1)
 BEGIN
   set @FLAG=@FLAG+1
   IF @FLAG=1
      begin
        set @sql='select * from '+@T
      end
  ELSE
      begin     
         set @sql=@sql+' union all select * from '+@t
      end
 END  
end

if @FLAG=0
begin
 select 0
 return -1
end
--创建一个零时试图
declare @tmpView varchar(4000)

set @tmpView='tmpV'+cast(DATEPART(YYYY,getDate()) as varchar(4))+
cast(DATEPART(MM,getDate()) as varchar(2))+
cast(DATEPART(DD,getDate()) as varchar(2))+
cast(DATEPART(HH,getDate()) as varchar(2))+
cast(DATEPART(SS,getDate()) as varchar(2))+
cast(DATEPART(MS,getDate()) as varchar(4))

set @sql='CREATE  VIEW '+@tmpView+ ' as '+@sql
print @sql

exec(@sql)
--临时视图创建完毕

到此视图＠tmpView 就创建好了．后面的查询都可以直接引用 @tmpView了．

在查询结束后，只需要在执行下面的语句即可
set @sql='drop view '+@tmpView
exec(@sql)