若现在有1000张表左右，表结构都是一样的 2列，第一列ID，第二列Con,
表名的增长规则是 [2012-01-01]\[2012-01-02]\[2012-01-03]，
以日期为表名的，记录公司每天所有人聊天软件聊天记录，
现在我想查 4月份整月
ID为138的人都所有聊天记录该怎么查呢？
不会要union all 30张表吧？
请大家帮帮忙
------解决方案--------------------
拼接动态查询字符串。
------解决方案--------------------
------解决方案--------------------
提种方法你可以参考下，你可以生成一个作业，每天执行一次，这个作业的作用是生成一张视图，视图是UNION所有表内容并附加日期列信息。
你的表应该也是每天执行一次的作业，所以在作业里添加生成视图的语句就可以了
这样每天作业更新视图内容，而你想查询某一时间段的信息只需要直接从生成的视图里面查询就可以了，不用每次都UNION所有表或者拼接SQL语句了。
举个例子：
- SQL code
CREATE TABLE [2012-03-14]( ID INT NOT NULL, Con VARCHAR(100) NOT NULL)INSERT INTO [2012-03-14]SELECT 1,'aa' UNIONSELECT 2,'bb' UNION SELECT 3,'cc' UNIONSELECT 138,'dd'CREATE TABLE [2012-03-15]( ID INT NOT NULL, Con VARCHAR(100) NOT NULL)INSERT INTO [2012-03-15]SELECT 1,'aa' UNIONSELECT 2,'bb' UNION SELECT 3,'cc' UNIONSELECT 138,'dd'DECLARE @Table TABLE(Id INT IDENTITY(1,1),TableName VARCHAR(100))DECLARE @Total INTDECLARE @Line INTDECLARE @Sql VARCHAR(8000)DECLARE @TableName VARCHAR(100)SET @Line = 1INSERT INTO @TableSELECT name FROM SYSOBJECTS WHERE name LIKE '20%'SELECT @Total = MAX(Id)FROM @TableIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'UV_ShowAllTable')BEGIN DROP VIEW UV_ShowAllTableENDSET @Sql = 'CREATE VIEW UV_ShowAllTable AS 'WHILE @Line <= @TotalBEGIN SELECT @TableName = TableName FROM @Table WHERE Id = @Line SET @Sql = @Sql + ' SELECT Id,Con,' + '''' + @TableName + '''' + ' AS Date FROM ' + '[' + @TableName + ']' IF @Line <> @Total BEGIN SET @Sql = @Sql + ' UNION ' END SET @Line = @Line + 1 ENDEXEC (@Sql)SELECT * FROM UV_ShowAllTableWHERE ID = 138 AND Date BETWEEN '2012-03-01' AND '2012-03-31'
------解决方案--------------------
------解决方案--------------------
- SQL code
--化解字符串不能超过8000的方法二--常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题 --下面就讨论这个问题: --创建测试数据if exists (select * from dbo.sysobjects where id = object_id(N '[tb] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1) drop table [tb] GO create table tb(单位名称 varchar(10),日期 datetime,销售额 int) insert into tb select 'A单位 ', '2001-01-01 ',100 union all select 'B单位 ', '2001-01-02 ',101 union all select 'C单位 ', '2001-01-03 ',102 union all select 'D单位 ', '2001-01-04 ',103 union all select 'E单位 ', '2001-01-05 ',104 union all select 'F单位 ', '2001-01-06 ',105 union all select 'G单位 ', '2001-01-07 ',106 union all select 'H单位 ', '2001-01-08 ',107 union all select 'I单位 ', '2001-01-09 ',108 union all select 'J单位 ', '2001-01-11 ',109 /*要求结果 日期 A单位 B单位 C单位 D单位 E单位 F单位 G单位 H单位 I单位 J单位 ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------2001-01-01 100 0 0 0 0 0 0 0 0 0 2001-01-02 0 101 0 0 0 0 0 0 0 0 2001-01-03 0 0 102 0 0 0 0 0 0 0 2001-01-04 0 0 0 103 0 0 0 0 0 0 2001-01-05 0 0 0 0 104 0 0 0 0 0 2001-01-06 0 0 0 0 0 105 0 0 0 0 2001-01-07 0 0 0 0 0 0 106 0 0 0 2001-01-08 0 0 0 0 0 0 0 107 0 0 2001-01-09 0 0 0 0 0 0 0 0 108 0 2001-01-11 0 0 0 0 0 0 0 0 0 109 */ --常规处理方法declare @sql varchar(8000) set @sql= 'select 日期=convert(varchar(10),日期,120) ' select @[email protected]+ ',[ '+单位名称 + ']=sum(case 单位名称 when ' ' '+单位名称+ ' ' ' then 销售额 else 0 end) ' from(select distinct 单位名称 from tb) a exec(@sql+ ' from tb group by convert(varchar(10),日期,120) ') --问题: 如果单位很多,这时,@SQL的值就会被截断,从而出错.--下面给出三种解决办法: --方法1. 多个变量处理 --定义变量,估计需要多少个变量才能保存完所有数据 declare @sql0 varchar(8000),@sql1 varchar(8000) --,...@sqln varchar(8000) --生成数据处理临时表 select id=identity(int,0,1),groupid=0 ,值= ',[ '+单位名称 + ']=sum(case 单位名称 when ' ' ' +单位名称+ ' ' ' then 销售额 else 0 end) ' into #temp from(select distinct 单位名称 from tb) a --分组临时表,判断慨最多多少个单位可以组合成一个不超过8000的字符串,这里取假设为5个 update #temp set groupid=id/5 --5为每组的单位个数 --生成SQL语句处理字符串 --初始化 select @sql0= ' ' ,@sql1= ' ' -- ... -- ,@sqln --得到处理字符串 select @[email protected]+值 from #temp where groupid=0 --第一个变量 select @[email protected]+值 from #temp where groupid=1 --第二个变量 --select @[email protected]+值 from #temp where groupid=n --第n个变量 --查询 exec( 'select 日期=convert(varchar(10),日期,120) ' [email protected][email protected] -- ...[email protected] + ' from tb group by convert(varchar(10),日期,120) ') --删除临时表 drop table #temp /* 优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分 缺点:要自行估计处理的数据,估计不足就会出错 */ --方法2. bcp+isql --因为要用到bcp+isql,所以需要这些信息 declare @servername varchar(250),@username varchar(250),@pwd varchar(250) select @servername= 'zj ' --服务器名 ,@username= ' ' --用户名 ,@pwd= ' ' --密码 declare @tbname varchar(50),@sql varchar(8000) --创建数据处理临时表 set @tbname= '[##temp_ '+convert(varchar(40),newid())+ '] ' set @sql= 'create table [email protected]+ '(值 varchar(8000)) insert into [email protected]+ ' values( ' 'create view ' +stuff(@tbname,2,2, ' ')+ ' as select 日期=convert(varchar(10),日期,120) ' ') ' exec(@sql) set @sql= 'insert into [email protected]+ ' select ' ',[ ' '+单位名称+ ' ']=sum(case 单位名称 when ' ' ' ' ' ' +单位名称+ ' ' ' ' ' ' then 销售额 else 0 end) ' ' from(select distinct 单位名称 from tb) a ' exec(@sql) set @sql= 'insert into [email protected]+ ' values( ' 'from tb group by convert(varchar(10),日期,120) ' ') ' exec(@sql) --生成创建视图的文件,注意使用了文件:c:\temp.txt set @sql= 'bcp ' [email protected]+ '' out 'c:\temp.txt' /S' ' [email protected]+ '' /U' [email protected]+ '' /P' [email protected]+ '' /c ' exec master..xp_cmdshell @sql --删除临时表 set @sql= 'drop table [email protected] exec(@sql) --调用isql生成数据处理视图 set @tbname=stuff(@tbname,2,2, ' ') set @sql= 'isql /S' [email protected] +case @username when ' ' then '' /E ' else '' /U' [email protected]+ '' /P' [email protected]+ '' ' end + ' /d' '+db_name()+ '' /i'c:\temp.txt' ' exec master..xp_cmdshell @sql --调用视图,显示处理结果 set @sql= 'select * from [email protected]+ ' drop view [email protected] exec(@sql) /* 优点:程序自动处理,不存在判断错误的问题 缺点:复杂,经过的步骤多,容易出错,而且需要一定的操作员权限 */ --方法3. 多个变量处理,综合了方法1及方法2的优点, 解决了方法1中需要人为判断的问题,自动根据要处理的数据量进行变量定义,同时又避免了方法2的繁琐 declare @sqlhead varchar(8000),@sqlend varchar(8000) ,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000) ,@i int,@ic varchar(20) --生成数据处理临时表 select id=identity(int,0,1),gid=0 ,a= ',[ '+单位名称 + ']=sum(case 单位名称 when ' ' ' +单位名称+ ' ' ' then 销售额 else 0 end) ' into # from(select distinct 单位名称 from tb) a --判断需要多少个变量来处理 select @i=max(len(a)) from # print @i set @[email protected] --分组临时表 update # set [email protected] select @i=max(gid) from # --生成数据处理语句 select @sqlhead= ' ' 'select 日期=convert(varchar(10),日期,120) ' ' ' ,@sqlend= ' ' ' from tb group by convert(varchar(10),日期,120) ' ' ' ,@sql1= ' ',@sql2= 'select ',@sql3= ' ',@sql4= ' ' while @i > =0 select @ic=cast(@i as varchar),@[email protected] ,@sql1= '@ [email protected]+ ' varchar(8000), [email protected] ,@[email protected]+ '@ [email protected]+ '= ' ' ' ', ' ,@sql3= 'select @ [email protected]+ '=@ [email protected]+ '+a from # where gid= [email protected] +char(13)[email protected] ,@[email protected]+ ',@ [email protected] select @sql1= 'declare '+left(@sql1,len(@sql1)-1)+char(13) ,@sql2=left(@sql2,len(@sql2)-1)+char(13) ,@sql3=left(@sql3,len(@sql3)-1) ,@sql4=substring(@sql4,2,8000) --执行 exec( @[email protected][email protected]+ ' exec( [email protected]+ '+ [email protected]+ '+ [email protected]+ ') ' ) --删除临时表 drop table # --方法3中,关键要做修改的是下面两句,其他基本上不用做改变: --生成数据处理临时表,修改a=后面的内容为相应的处理语句 select id=identity(int,0,1),gid=0 ,a= ',[ '+code+ ']=sum(case b.c_code when ' ' ' +code+ ' ' ' then b.value else 0 end) ' into # from #Class --生成数据处理语句,[email protected],@sqlend赋值为相应的处理语句头和尾 select @sqlhead= ' ' 'select a.id,a.name,a.code ' ' ' ,@sqlend= ' ' ' from #Depart a,#Value b where a.Code=b.d_Code group by a.id,a.code,a.name ' ' ' ,@sql1= ' ',@sql2= 'select ',@sql3= ' ',@sql4= ' '