semovy

SQL server2005常用的函數(shù)和分頁的幾個解決方案：

使用時間和日期的函數(shù)
getdate():獲取系統(tǒng)當(dāng)前時間
dateadd(datepart,number,date):計算在一個時間的基礎(chǔ)上增加一個時間后的新時間值,比如：dateadd(yy,30,getdate())
datediff(datepart,startdate,enddate):計算兩個時間的差值,比如：datediff(yy,getdate(),'2008-08-08')
dataname(datepart,date):獲取時間不同部分的值，返回值為字符串
datepart(datepart,date):和datename相似，只是返回值為整型
day(date):獲取指定時間的天數(shù)
month(date):獲取指定時間的月份
year(date):獲取指定時間的年份

問題1:
表A是一個不斷有新記錄增加的表，字段t就是記錄的插入時間,現(xiàn)在想知道每10秒鐘插入到該表的
記錄總數(shù)大于N條的時間和記錄條數(shù)，并按由大到小的順序排序，請寫出完成該功能的SQL語句。
解決方案：
declare @t datetime
select @t=min(t) from B
select max(t) as maxT,min(t) as minT,count(*) as Num from B group by
datediff(ss,@t,t)/10 having count(*)>1 order by count(*) desc

全局變量：
以@@開頭，且不能由用戶自定義，下面來看幾個常用的全局變量：
@@rowcount ：表示最近一個語句影響的行數(shù)。
@@error:上一條sql語句返回的錯誤信息。
@@identity:對有標(biāo)識列的表而言，系統(tǒng)會為表每生成一個新的標(biāo)識號，這個變量記錄的就是最近產(chǎn)生的id。

rowcount:會話級全局變量,比如set rowcount 3 ,就是設(shè)置返回的記錄數(shù)最多是3條，此變量的作用類似于
top子句，TOP 子句適用于指定了該子句的單個 SELECT 語句。SET ROWCOUNT 將一直有效，直到執(zhí)行另一個
SET ROWCOUNT 語句，例如 SET ROWCOUNT 0 將關(guān)閉該選項。

indetity_insert：會話級全局變量
任何時候，一個會話中只有一個表的 IDENTITY_INSERT 屬性可以設(shè)置為 ON。如果某個表已將此屬性設(shè)
置為 ON，則對另一個表發(fā)出 SET IDENTITY_INSERT ON 語句時，SQL Server 2005 將返回一個錯誤信息，
指出 SET IDENTITY_INSERT 已設(shè)置為 ON，并報告已將其屬性設(shè)置為 ON 的表。
如果插入值大于表的當(dāng)前標(biāo)識值，則 SQL Server 自動將新插入值作為當(dāng)前標(biāo)識值使用。
SET IDENTITY_INSERT 的設(shè)置是在執(zhí)行或運行時設(shè)置的，而不是在分析時設(shè)置的。
dbcc checkident (table_name,reseed,n)：此語句的功能是更正列的當(dāng)前標(biāo)識值，把table_name的標(biāo)
識符設(shè)置為n。如果當(dāng)標(biāo)識列中存在 PRIMARY KEY 或 UNIQUE 約束時，無效標(biāo)識信息會導(dǎo)致錯誤消息 2627。

SQL Server的幾個分頁解決方案：
解決方案1：
declare @pageIndex int,@pageSize int,@recordNum int
set @pageIndex=3
set @pageSize=3
select top(@pageSize) * from Grade where id not in (select top
((@pageIndex-1)*@pageSize) id from Grade)//顯示出第 7 到 第 9 條

select @recordNum=count(*) from Grade//顯示總記錄數(shù)

點評：效率不高，而且取出來的數(shù)據(jù)是按id排序的。如果想按其他字段排序就不行。

解決方案二：
declare @id int
--set rowcount 3
--select @id=id from Grade
select top 3 @id=id from Grade//這句等效于上面注視的兩句
select top 3 * form Grade where id>@id//查詢出第 4 到第 6 條記錄

點評：效率比方案一稍高，但是取出來的數(shù)據(jù)是也按id排序的。如果想按其他字段排序就不行

解決方案三：
create table #table(new_id int identity(1,1) primary key,id int)
insert into #table(id) select id from grade
select a.* from grade a join #table b   on (b.new_id between 6 and 9) and a.id=b.id
//取出第 6 到第 9 條，
點評：其原理是把要分頁的表的主鍵插入到臨時表中，臨時表的字段就是一個標(biāo)識列和要分
頁的表的主鍵列（包括是多個主鍵的情況，上面的兩種方法就不行）。特點是效率高，取出
來的數(shù)據(jù)能根據(jù)自己要求的字段排序。

臨時表：
上面的解決方案三就是使用了臨時表，它與永久表相似，但臨時表存儲在 tempdb 中，當(dāng)不
再使用時會自動刪除。
臨時表有兩種類型：本地和全局。它們在名稱、可見性以及可用性上有區(qū)別。本地臨時表
的名稱以單個數(shù)字符號 (#) 打頭；它們僅對當(dāng)前的用戶連接是可見的；當(dāng)用戶從 SQL Server
實例斷開連接時被刪除。全局臨時表的名稱以兩個數(shù)字符號 (##) 打頭，創(chuàng)建后對任何用戶
都是可見的，當(dāng)所有引用該表的用戶從 SQL Server 斷開連接時被刪除。

表級變量：
象上面分頁的例子也可以使用表級變量，象下面這樣：

declare @table table (newid int identiey(1,1),id int)
insert into @table(id) select id from grade
select a.* from grade a join @table b   on (b.new_id between 6 and 9) and a.id=b.id
這種方式比使用臨時表分頁效率更高。

字符串函數(shù)：
left(stringExp,intExp):取字符串左邊多少個字符
len(strExp):計算指定字符串的長度
char(intExp):根據(jù)指定字符的ASCII碼返回相應(yīng)的字符
ascii(strExp)：將一個字符轉(zhuǎn)換成相應(yīng)的ascii碼
lower(strExp):將字符串轉(zhuǎn)換為小寫
Upper(strExp):將字符串轉(zhuǎn)換為大寫
Ltrim(strExp):去字符串左邊的空格
rtrim(strExp):去字符串右邊的空格
substring(strExp,intExp,intExp):按指定的索引截取一個字符串的子字符串
replace(strExp,strOldStr,strNewStr):將字符串中的部分內(nèi)容用新的字符串來代替

系統(tǒng)其他轉(zhuǎn)換函數(shù):
isnull(邏輯表達(dá)式,代替值):判斷邏輯表達(dá)式是否為null，是的話就用指定的值代替。
convert()和cast：
CAST 函數(shù)和 CONVERT 函數(shù)還可用于獲取各種特殊數(shù)據(jù)格式，并可用于選擇列表、WHERE
子句以及允許使用表達(dá)式的任何位置中。如果希望 Transact-SQL 程序代碼符合 SQL-92，
請使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的功能，請使用 CONVERT
而不要使用 CAST。
使用 CAST 或 CONVERT 時，需要提供以下信息：
要轉(zhuǎn)換的表達(dá)式；例如，銷售報表要求銷售數(shù)據(jù)從貨幣數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)。
要將指定的表達(dá)式轉(zhuǎn)換為的數(shù)據(jù)類型，例如 varchar 或其他 SQL Server 系統(tǒng)數(shù)據(jù)類型。
除非將被轉(zhuǎn)換的值存儲起來，否則轉(zhuǎn)換僅在 CAST 函數(shù)或 CONVERT 函數(shù)的作用時間范圍內(nèi)有效。
如果轉(zhuǎn)換時沒有指定數(shù)據(jù)類型的長度，則 SQL Server 自動將 30 作為長度值。

問題：
有一張商品表，里面三個字段：購買日期，購買人，價格。現(xiàn)在要統(tǒng)計每個購買人每一
季度的消費總價，并算出小計（也就是這一年消費的總價）

解決方案：

創(chuàng)建一張shop表：
create table shop
(
selldate datetime default(getdate()),
name varchar(20),
price money,
)
加入一些測試數(shù)據(jù)：
insert into shop(selldate,name,price) values('2006-05-12','張三',75)
insert into shop(selldate,name,price) values('2006-07-12','張三',50)
insert into shop(selldate,name,price) values('2006-09-12','張三',43)
insert into shop(selldate,name,price) values('2006-11-12','李四',67)
insert into shop(selldate,name,price) values('2006-01-12','李四',98)
insert into shop(selldate,name,price) values('2006-06-12','李四',100)
insert into shop(selldate,name,price) values('2006-08-12','王五',134)
insert into shop(selldate,name,price) values('2006-04-12','王五',165)
insert into shop(selldate,name,price) values('2006-10-12','王五',85)
insert into shop(selldate,name,price) values('2006-08-12','趙六',11)
insert into shop(selldate,name,price) values('2006-12-12','趙六',165)
insert into shop(selldate,name,price) values('2006-02-12','趙六',17)
insert into shop(selldate,name,price) values('2006-01-12','趙六',198)

select * from shop

解決問題的select語句：
select name 姓名,
sum(case when datepart(q,selldate)=1 then price else 0 end) 第一季度,
sum(case when datepart(q,selldate)=2 then price else 0 end) 第二季度,
sum(case when datepart(q,selldate)=3 then price else 0 end) 第三季度,
sum(case when datepart(q,selldate)=4 then price else 0 end) 第四季度,
sum(price) 小計
from shop   group by name