1.sql server里的while循環(huán),即通常的for循環(huán)
while
Begin
....
end
例子：
declare @num int
declare @datediff int
select @datediff=datediff(day,'2007-12-12','2008-9-10)
set @num=0
while @num<@datediff
begin
insert into cms_count values('192.168.1.174','1','寶寶','http://baobao.com/','1280*780','IE','Vasta','www.163.com',dateadd(day,@num,'2007-12-12') )
set @num=@num+1
end
2.sql游標
declare 游標名 for 語句
open 游標
fetch next from 游標 into 變量
while @@fetch_status=0
begin
end
close 游標
deallocate 游標
fetch next from 游標 into 變量
end
3.快速查看表字段信息
sp_help 表名
4.case語句
case when 條件 then 結果 end
例子：
select case when '2009-12-17 00:00:00.000' < convert(varchar(20),getdate(),120) then 1 else 2 end
復雜一點的例子
set @sql = 'select '''' F1,ct.referenceNo F2,'''' F3,'''' F4,ct.contractNo F5,
(cus.firstname+cus.lastname) F6,
case when p.name=city.name then (p.name+cus.street) else (p.name+city.name+cus.street) end F7
,cus.zipcode F8,''待開票'' F9,ct.warrantyPrice F10
from contract ct left join customer cus on ct.customer=cus.id
left join province p on cus.province=p.id
left join city on cus.city=city.id
where ct.id in (' + @contractIds + ')'
5. convert類型轉換
convert(varchar, getdate(), 120)
輸出：2009-12-17 10:05:30.000
convert和cast功能類似。
6.Substring截取字符串
substring(convert(varchar, getdate(), 120), 1,10)+' 00:00:00'
輸出凌晨時間：2009-12-17 00:00:00
7.cast類型轉換
cast(substring(convert(varchar, getdate(), 120), 1,10) + ' 00:00:00' as datetime)
輸出：2009-12-17 00:00:00.000
8.數據快照
創(chuàng)建數據庫快照
create database myTurkey_tr on
(name=turkey_tr,filename='d:\mr_TurkeyC.ss')
as snapshot of turkey_tr
刪除數據庫快照
drop database myTurkey_tr
還原數據庫快照
RESTORE DATABASE turkey_tr FROM
DATABASE_SNAPSHOT = 'myTurkey_tr'
9.charindex返回字符串中指定表達式的起始位置
charindex('ab','sssssabcccc',3)
返回6，即從第二個參數字符串的第三個字符開始查找第一個參數字符'ab'。
10.把a表中的一個字段改為b表中對應字段的一個值，a,b有對應關系
update salesdata set contract=ctp.summaryid from #contracttemp ctp inner join salesdata st
on ctp.warrantypurchasedate=st.purchasedate and ctp.referenceNo=st.transactionNo
where st.inavailable=0
11.count的用法，把表中字段q4的值大于5的和字段q3比較得出百分率
convert(varchar,(count(case when nsr.q4>5 then nsr.q4 end)*100)/count(nsr.q3))+''%''
12.利用datediff計算時間差
convert(varchar,count(case when (datediff(minute,sr.createdTime,sr.closedTime)/(24*60))>=7 then sr.closedTime end)) F9,
convert(varchar,count(case when (datediff(minute,sr.createdTime,sr.closedTime)/(24*60))>=14 then sr.closedTime end)) F10,
