SQL SERVER2005中的那些事

 這幾天的工作都是和資料庫打交道，今天抽空總結下最近遇到的問題。

1.datetime字段模糊查詢？

USE SICCDB 

GO 

SELECT 

    StartTime 

FROM 

    dbo.WGS_MainCDR 

WHERE 

    StartTime LIKE '2012-06-20%' 

大家猜測下，這樣可以查詢出結果嗎？ 

注：表中時間字段查詢的結果：

<a href="http://blog.51cto.com/attachment/201208/100226251.png" target="_blank"></a>

那上面那句的T-SQL語句執行的結果呢？我們來看下：

<a href="http://blog.51cto.com/attachment/201208/100543224.png" target="_blank"></a>

我們發現查詢的結果什麼也沒有。上面的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查詢

    STARTTIME BETWEEN '2012-06-20 12:00:00.000' AND '2012-06-21 12:00:00.000' 

datediff()函數查詢

    DATEDIFF(yyyy,STARTTIME,'2012')=0 

第二種方法适用String外的類型；

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 --此判斷不嚴密  

print 'object not in current database'  

if OBJECTPROPERTY(@objectId,'IsTable') &lt; &gt; 1 -- 判斷對象是否是table  

print 'The object is not table'  

select @ident=status&amp;0x80 from syscolumns where id=@objectid and status&amp;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 &lt; &gt;-1  

if @@fetch_status &lt; &gt;-2  

if @xtype not in(189,34,35,99,98) --timestamp不需處理，image,text,ntext,sql_variant 暫時不處理  

set @column=@column+case when len(@column)=0 then'' else ','end+@name  

set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','  

+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  

close syscolumns_cursor  

deallocate syscolumns_cursor  

set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename  

print '--'+@sql  

exec(@sql)  

print 'SET IDENTITY_INSERT '+@TableName+' OFF'  

 exec UspOutputData 表名

<a href="http://blog.51cto.com/attachment/201208/103526721.png" target="_blank"></a>

在上面執行的結果中點選右鍵--》将結果另存為.sql檔案就大功告成啦。

3.求時間段的平均值

我們同樣用DATEDIFF（）函數來做。DATEDIFF(SECOND,STARTTIME,ENDTIME)用結束時間-開始時間取秒為機關。外面用AVG()函數，AVG(DATEDIFF(SECOND,STARTTIME,ENDTIME))

4.比較糾結的二表查詢，結果為統計和行轉列

第一個表WGS_MainCDR:

<a href="http://blog.51cto.com/attachment/201208/104529388.png" target="_blank"></a>

第二個表WGS_QueuePhase:

<a href="http://blog.51cto.com/attachment/201208/104631165.png" target="_blank"></a>

我們需要查詢：每日分機進線量（CALLTYPE=0）、進線應答量（CALLTYPE=0和ANSWERTIME不為空）、外撥量（CALLTYPE=1）、外撥應答量（CALLTYPE=1和ANSWERTIME不為空）、平均通話時長(總時長/每日進線量)

 先單個查詢出統計日期、分機進線量、分機進線應答量、分機外撥量、分機外撥應答量、進線通話時長和外撥通話時長：

        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 

    *, 

    CASE 分機進線量 WHEN 0 THEN 0 ELSE ROUND((進線通話時長 / 分機進線量),2) END AS 平均進線通話時長, 

    CASE 分機進線量 WHEN 0 THEN 0 ELSE ROUND((外撥通話時長 / 分機進線量),2) END AS 平均外撥通話時長 

FROM ( 

    SELECT  

) A 

 本文轉自shyy8712872 51CTO部落格，原文連結：http://blog.51cto.com/shuyangyang/962956，如需轉載請自行聯系原作者