这几天的工作都是和数据库打交道，今天抽空总结下最近遇到的问题。
1.datetime字段模糊查询？
- USE SICCDB
- GO
- SELECT
- StartTime
- FROM
- dbo.WGS_MainCDR
- WHERE
- StartTime LIKE '2012-06-20%'
大家猜测下，这样可以查询出结果吗？
注：表中时间字段查询的结果：
那上面那句的T-SQL语句执行的结果呢？我们来看下：
我们发现查询的结果什么也没有。上面的T-SQL语句在05中是什么都没有查询到，在其他数据库里可能会出现错误。
要解决这种问题，可以有三种方法：
- 使用convert()函数转换datetime类型为String类型查询
- USE SICCDB
- GO
- SELECT
- STARTTIME
- FROM
- dbo.WGS_MainCDR
- WHERE
- CONVERT(CHAR(10),STARTTIME,120) LIKE '2012-06-20%'
- between查询
- USE SICCDB
- GO
- SELECT
- STARTTIME
- FROM
- dbo.WGS_MainCDR
- WHERE
- STARTTIME BETWEEN '2012-06-20 12:00:00.000' AND '2012-06-21 12:00:00.000'
- datediff()函数查询
- USE SICCDB
- GO
- SELECT
- STARTTIME
- FROM
- dbo.WGS_MainCDR
- WHERE
- DATEDIFF(yyyy,STARTTIME,'2012')=0
第一种方法应该适用与任何数据类型;具体用法看这里
第二种方法适用String外的类型；
第三种方法则是为date类型定制的比较实用快捷的方法。具体用法看这里。
2.SQLSERVER2005中怎么导出包含插入语句的SQL？
额，是不是你也遇到过这个问题，很纠结呢？通常情况下，SQLSERVER2005中并没有这种功能？只可以导出一些创建数据库和建表的SQL。
我们可以写个存储过程：
- CREATE PROCEDURE dbo.UspOutputData
- @tablename sysname
- AS
- declare @column varchar(1000)
- declare @columndata varchar(1000)
- declare @sql varchar(4000)
- declare @xtype tinyint
- declare @name sysname
- declare @objectId int
- declare @objectname sysname
- declare @ident int
- set nocount on
- set @objectId=object_id(@tablename)
- if @objectId is null -- 判断对象是否存在
- begin
- print 'The object not exists'
- return
- end
- set @objectname=rtrim(object_name(@objectId))
- if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
- begin
- print 'object not in current database'
- return
- end
- if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是table
- begin
- print 'The object is not table'
- return
- end
- select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
- if @ident is not null
- print 'SET IDENTITY_INSERT '+@TableName+' ON'
- declare syscolumns_cursor cursor
- for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
- open syscolumns_cursor
- set @column=''
- set @columndata=''
- fetch next from syscolumns_cursor into @name,@xtype
- while @@fetch_status < >-1
- begin
- if @@fetch_status < >-2
- begin
- if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
- begin
- set @column=@column+case when len(@column)=0 then'' else ','end+@name
- set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','
- end
- +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
- when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
- when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
- when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
- when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
- else @name end
- end
- end
- fetch next from syscolumns_cursor into @name,@xtype
- end
- close syscolumns_cursor
- deallocate syscolumns_cursor
- set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename
- print '--'+@sql
- exec(@sql)
- if @ident is not null
- print 'SET IDENTITY_INSERT '+@TableName+' OFF'
- GO
exec UspOutputData 表名
在上面执行的结果中点击右键--》将结果另存为.sql文件就大功告成啦。
3.求时间段的平均值
我们同样用DATEDIFF（）函数来做。DATEDIFF(SECOND,STARTTIME,ENDTIME)用结束时间-开始时间取秒为单位。外面用AVG()函数，AVG(DATEDIFF(SECOND,STARTTIME,ENDTIME))
4.比较纠结的二表查询，结果为统计和行转列
第一个表WGS_MainCDR:
第二个表WGS_QueuePhase:
我们需要查询：每日分机进线量（CALLTYPE=0）、进线应答量（CALLTYPE=0和ANSWERTIME不为空）、外拨量（CALLTYPE=1）、外拨应答量（CALLTYPE=1和ANSWERTIME不为空）、平均通话时长(总时长/每日进线量)
先单个查询出统计日期、分机进线量、分机进线应答量、分机外拨量、分机外拨应答量、进线通话时长和外拨通话时长：
- SELECT
- CONVERT(CHAR(10),ALERTTIME, 120) AS 统计日期,
- SUM(CASE WHEN M.CALLTYPE = 0 THEN 1 ELSE 0 END) AS 分机进线量,
- SUM(CASE WHEN M.CALLTYPE = 0 AND E.ANSWERTIME IS NOT NULL THEN 1 ELSE 0 END) AS 分机进线应答量,
- SUM(CASE WHEN M.CALLTYPE = 1 THEN 1 ELSE 0 END) AS 分机外拨量,
- SUM(CASE WHEN M.CALLTYPE = 1 AND E.ANSWERTIME IS NOT NULL THEN 1 ELSE 0 END) AS 分机外拨应答量,
- SUM(CASE WHEN M.CALLTYPE = 0 AND E.ANSWERTIME IS NOT NULL THEN DATEDIFF(SECOND,E.ANSWERTIME,E.ENDTIME) ELSE 0 END) AS 进线通话时长,
- SUM(CASE WHEN M.CALLTYPE = 1 AND E.ANSWERTIME IS NOT NULL THEN DATEDIFF(SECOND,E.ANSWERTIME,E.ENDTIME) ELSE 0 END) AS 外拨通话时长
- FROM
- WGS_ExtensionPhase E
- LEFT JOIN WGS_MainCDR M ON E.UCID = M.UCID
- GROUP BY CONVERT(CHAR(10),ALERTTIME, 120)
下面将行转列：
- use siccdb
- go
- SELECT
- *,
- CASE 分机进线量 WHEN 0 THEN 0 ELSE ROUND((进线通话时长 / 分机进线量),2) END AS 平均进线通话时长,
- CASE 分机进线量 WHEN 0 THEN 0 ELSE ROUND((外拨通话时长 / 分机进线量),2) END AS 平均外拨通话时长
- FROM (
- SELECT
- CONVERT(CHAR(10),ALERTTIME, 120) AS 统计日期,
- SUM(CASE WHEN M.CALLTYPE = 0 THEN 1 ELSE 0 END) AS 分机进线量,
- SUM(CASE WHEN M.CALLTYPE = 0 AND E.ANSWERTIME IS NOT NULL THEN 1 ELSE 0 END) AS 分机进线应答量,
- SUM(CASE WHEN M.CALLTYPE = 1 THEN 1 ELSE 0 END) AS 分机外拨量,
- SUM(CASE WHEN M.CALLTYPE = 1 AND E.ANSWERTIME IS NOT NULL THEN 1 ELSE 0 END) AS 分机外拨应答量,
- SUM(CASE WHEN M.CALLTYPE = 0 AND E.ANSWERTIME IS NOT NULL THEN DATEDIFF(SECOND,E.ANSWERTIME,E.ENDTIME) ELSE 0 END) AS 进线通话时长,
- SUM(CASE WHEN M.CALLTYPE = 1 AND E.ANSWERTIME IS NOT NULL THEN DATEDIFF(SECOND,E.ANSWERTIME,E.ENDTIME) ELSE 0 END) AS 外拨通话时长
- FROM
- WGS_ExtensionPhase E
- LEFT JOIN WGS_MainCDR M ON E.UCID = M.UCID
- GROUP BY CONVERT(CHAR(10),ALERTTIME, 120)
- ) A
本文转自shyy8712872 51CTO博客，原文链接：http://blog.51cto.com/shuyangyang/962956，如需转载请自行联系原作者
