semovy

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

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

問題1:
表A是一個不斷有新記錄增加的表，字段t就是記錄的插入時間,現在想知道每10秒鐘插入到該表的
記錄總數大于N條的時間和記錄條數，并按由大到小的順序排序，請寫出完成該功能的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 ：表示最近一個語句影響的行數。
@@error:上一條sql語句返回的錯誤信息。
@@identity:對有標識列的表而言，系統會為表每生成一個新的標識號，這個變量記錄的就是最近產生的id。

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

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

點評：效率不高，而且取出來的數據是按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 條記錄

點評：效率比方案一稍高，但是取出來的數據是也按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 條，
點評：其原理是把要分頁的表的主鍵插入到臨時表中，臨時表的字段就是一個標識列和要分
頁的表的主鍵列（包括是多個主鍵的情況，上面的兩種方法就不行）。特點是效率高，取出
來的數據能根據自己要求的字段排序。

臨時表：
上面的解決方案三就是使用了臨時表，它與永久表相似，但臨時表存儲在 tempdb 中，當不
再使用時會自動刪除。
臨時表有兩種類型：本地和全局。它們在名稱、可見性以及可用性上有區別。本地臨時表
的名稱以單個數字符號 (#) 打頭；它們僅對當前的用戶連接是可見的；當用戶從 SQL Server
實例斷開連接時被刪除。全局臨時表的名稱以兩個數字符號 (##) 打頭，創建后對任何用戶
都是可見的，當所有引用該表的用戶從 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
這種方式比使用臨時表分頁效率更高。

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

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

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

解決方案：

創建一張shop表：
create table shop
(
selldate datetime default(getdate()),
name varchar(20),
price money,
)
加入一些測試數據：
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