|
實(shí)用函數(shù)方法
由于有些知識(shí)很少被用到，但真需要用時(shí)卻忘記了又焦頭爛額的到處找。
現(xiàn)在將這些‘冷門“卻有效的小知識(shí)貢獻(xiàn)出來，以備不時(shí)之需。
存儲(chǔ)過程中的 '''' 相當(dāng)于數(shù)據(jù)庫中的‘ 單引號(hào)
DECLARE @str VARCHAR(100) SET @str='''aaa''' SELECT REPLACE(@str,'''',''') ：'aaa'
rtrim :使用 LTRIM 刪除字符變量中的前導(dǎo)空格 ; RTRIM 刪除字符變量中的尾隨空格
rtrim(ltrim(splitdata))
用select into 把數(shù)據(jù)放到臨時(shí)表中，按交費(fèi)期限排序，并加上id
select identity(int,1,1) as id,FeeGUID,PayLimit,BgnDate,EndDate,Amount,TestIsRight into #tmpfee --select into不需要提前聲明臨時(shí)表#tmpfee from z_fee where RentGUID = @strRentGUID order by PayLimit drop table #tmpfee
用insert into select 創(chuàng)建臨時(shí)表 插入自增列
Create Table #Temp_ProjectCodeList ( RowId int identity(1,1) ,ProjectCode varchar(100) ) Insert Into #Temp_ProjectCodeList(ProjectCode) Select ProjCode From p_Project Where Level = 2 Set @MaxCount = @@RowCount Set @Count = 1 While @Count @MaxCount Begin Select @ProjectCode = ProjectCode From #Temp_ProjectCodeList Where RowId = @Count Exec usp_cb_BuildHsCost @ProjectCode, @IsExistHsCost, @IsUpdateCsCost --調(diào)整計(jì)數(shù)器 Set @Count = @Count + 1 End
Return 執(zhí)行不成功，中斷執(zhí)行
If Exists(select 8 from cb_HsCost where ProjectCode = @ProjectCode and IsJianAn = 1) If @ProjectGUID Is Null Begin Print '[' + @ProjectCode + ']：當(dāng)前指定的項(xiàng)目在當(dāng)前系統(tǒng)中不存在！' Return -1 End
得到包含前月在內(nèi)的一年時(shí)間
SET @dtBeginDate = getdate() SET @dtEndDate = dateadd(month,-1,dateadd(year,1,@dtBeginDate))
高能預(yù)警
DATEPART ( datepart , date ) datepart函數(shù)中一周是周日到周六，而我們通常認(rèn)為一周是周一到周日 返回表示指定日期的指定日期部分的整數(shù)
本周第一天 (星期1)
select dateadd(wk, datediff(wk,0,getdate()), 0)
本周最后一天（星期天）
select dateadd(wk, datediff(wk,0,getdate()), 6)
得到上周一的日期： SELECT DATEADD(day,-DATEPART(weekday,getdate())-5,getdate())
得到上周日的日期：
SELECT DATEADD(day,-DATEPART(weekday,getdate())+1,getdate())
得到上個(gè)月月末日期：
SELECT dateadd(day,-datepart(day,getdate()),getdate())
上月第一天
SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)
本月第一天
select dateadd(dd,-datepart(dd,getdate())+1,getdate())
本月最后一天(當(dāng)前為2011-03-31時(shí)會(huì)出錯(cuò)) 選用：
select dateadd(dd,-DAY(dateadd(mm,1,'2011-12-20')) ,dateadd(mm,1,'2011-12-20')) select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))
下月第一天 select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))
下月最后一天
SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+' 23:59:59'
季度第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
季度最后一天（直接推算法）
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()),120)+'1')
季度的最后一天（CASE判斷法）
select DATEADD(Month,DATEPART(Quarter,getdate())*3-Month(getdate()),getdate())
本月第一個(gè)星期一
SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '')
今年第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
今年最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0))
指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
指定日期所在周的任意星期幾
A. 星期天做為一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
B. 星期一做為一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
周內(nèi)的第幾日 select datepart(weekday,getdate()) as 周內(nèi)的第幾日
年內(nèi)的第幾周
select datepart(week,getdate()) as 年內(nèi)的第幾周
年內(nèi)的第幾季
select datepart(quarter,getdate()) as 年內(nèi)的第幾季
快速高效創(chuàng)建數(shù)字輔助表
--創(chuàng)建數(shù)字輔助表 SET NOCOUNT ON IF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums; CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY ); DECLARE <a href='http://www./members/wx2197377149'>@max</a> AS INT ,@rc AS INT ; SET @max=10000; SET @rc=1; INSERT INTO dbo.Nums VALUES (1); WHILE @rc * 2 <a href='http://www./members/wx2197377149'>@max</a> BEGIN INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums ; SET @rc = @rc * 2; END INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums WHERE n + @rc @max; SELECT COUNT (n) FROM Nums
練習(xí)：將下面表1每行字符串轉(zhuǎn)化為表2格式
/*PlanDetailID Description 1 課程詳細(xì)安排1,課程詳細(xì)安排1.1,課程詳細(xì)安排1.2,課程詳細(xì)安排1.3 2 課程詳細(xì)安排2,課程詳細(xì)安排2.1,課程詳細(xì)安排2.2 3 課程詳細(xì)安排3,課程詳細(xì)安排3.1,課程詳細(xì)安排3.2,課程詳細(xì)安排3.3,課程詳細(xì)安排3.4 4 課程詳細(xì)安排4 5 課程詳細(xì)安排5 轉(zhuǎn)化為： PlanDetailID pos Description 1 1 課程詳細(xì)安排1 1 2 課程詳細(xì)安排1.1 1 3 課程詳細(xì)安排1.2 1 4 課程詳細(xì)安排1.3 2 1 課程詳細(xì)安排2 2 2 課程詳細(xì)安排2.1 2 3 課程詳細(xì)安排2.2 3 1 課程詳細(xì)安排3 3 2 課程詳細(xì)安排3.1 3 3 課程詳細(xì)安排3.2 3 4 課程詳細(xì)安排3.3 3 5 課程詳細(xì)安排3.4 4 1 課程詳細(xì)安排4 5 1 課程詳細(xì)安排5 */
參考SQL：
--生成副本，按逗號(hào)的個(gè)數(shù)，n為逗號(hào)的位置（默認(rèn)第一位為逗號(hào)） SELECT PlanDetailID , Description , n FROM dbo.T_PlanDetail INNER JOIN dbo.Nums ON n LEN(Description) + 1 --若無AND，則表示按字符個(gè)數(shù)來生成行數(shù) AND SUBSTRING(',' + Description, n, 1) = ',' ; --將含有逗號(hào)時(shí)候的行輸出 --計(jì)算每一個(gè)字符串的長度 SELECT PlanDetailID , SUBSTRING(Description, n, CHARINDEX(',', Description + ',', n) - n) AS element --元素的長度等于下一個(gè)逗號(hào)的位置減該元素的開始位置 FROM dbo.T_PlanDetail INNER JOIN dbo.Nums ON n LEN(Description) + 1 --若無AND，則表示按字符個(gè)數(shù)來生成行數(shù) AND SUBSTRING(',' + Description, n, 1) = ',' ; --將含有逗號(hào)時(shí)候的行輸出 --計(jì)算每個(gè)字符串在數(shù)組中的位置,按PlanDetailID 分區(qū)，按 n 排序 SELECT PlanDetailID ,ROW_NUMBER() OVER(PARTITION BY PlanDetailID ORDER BY n) AS pos, SUBSTRING(Description, n, CHARINDEX(',', Description + ',', n) - n) AS element FROM dbo.T_PlanDetail INNER JOIN dbo.Nums ON n LEN(Description) + 1 --若無AND，則表示按字符個(gè)數(shù)來生成行數(shù) AND SUBSTRING(',' + Description, n, 1) = ',' ; --將含有逗號(hào)時(shí)候的行輸出
在sql server中經(jīng)常有這樣的問題：
一個(gè)表采用了自動(dòng)編號(hào)的列之后，由于測試了好多數(shù)據(jù)，自動(dòng)編號(hào)已累計(jì)了上萬個(gè)。
現(xiàn)在正是要用這個(gè)表了，測試數(shù)據(jù)已經(jīng)刪了，遺留下來的問題 就是在錄入新的數(shù)據(jù)，編號(hào)只會(huì)繼續(xù)增加，已使用過的但已刪除的編號(hào)就不能用了， 誰知道如何解決此問題？ truncate命令不但會(huì)清除所有的數(shù)據(jù)，還會(huì)將IDENTITY的SEED的值恢復(fù)到原是值。
|
