你真的會玩SQL嗎？實用函式方法彙總

歡醉發表於2015-11-18

實用函式方法

由於有些知識很少被用到，但真需要用時卻忘記了又焦頭爛額的到處找。

現在將這些‘冷門“卻有效的小知識貢獻出來，以備不時之需。

儲存過程中的 '''' 相當於資料庫中的‘ 單引號
DECLARE @str VARCHAR(100)
SET @str='''aaa'''
SELECT REPLACE(@str,'''','"')
："aaa"

儲存過程中的 '''' 相當於資料庫中的‘ 單引號

DECLARE @str VARCHAR(100)

SET @str='''aaa'''

SELECT REPLACE(@str,'''','"')

："aaa"

rtrim :使用 LTRIM 刪除字元變數中的前導空格 ; RTRIM 刪除字元變數中的尾隨空格
rtrim(ltrim(splitdata))

1 2	rtrim :使用 LTRIM 刪除字元變數中的前導空格 ; RTRIM 刪除字元變數中的尾隨空格 rtrim(ltrim(splitdata))

-- 用select into 把資料放到臨時表中，按交費期限排序，並加上id
select identity(int,1,1) as id,FeeGUID,PayLimit,BgnDate,EndDate,Amount,TestIsRight 
into #tmpfee --select into不需要提前宣告臨時表#tmpfee
from z_fee 
where RentGUID = @strRentGUID
order by PayLimit

drop table #tmpfee

-- 用select into 把資料放到臨時表中，按交費期限排序，並加上id

select identity(int,1,1) as id,FeeGUID,PayLimit,BgnDate,EndDate,Amount,TestIsRight

into #tmpfee --select into不需要提前宣告臨時表#tmpfee

from z_fee

where RentGUID = @strRentGUID

order by PayLimit

drop table #tmpfee

用insert into select 建立臨時表 插入自增列
  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

            --調整計數器
            Set @Count = @Count + 1
        End

用insert into select 建立臨時表插入自增列

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

--調整計數器

Set @Count = @Count + 1

End

Return 執行不成功，中斷執行
If Exists(select 8 from cb_HsCost where ProjectCode = @ProjectCode and IsJianAn = 1)
   If @ProjectGUID Is Null
    Begin
        Print '[' + @ProjectCode + ']：當前指定的專案在當前系統中不存在！'
        Return -1
    End

Return 執行不成功，中斷執行

If Exists(select 8 from cb_HsCost where ProjectCode = @ProjectCode and IsJianAn = 1)

If @ProjectGUID Is Null

Begin

Print '[' + @ProjectCode + ']：當前指定的專案在當前系統中不存在！'

Return -1

End

得到包含前月在內的一年時間
SET @dtBeginDate = getdate()
SET @dtEndDate = dateadd(month,-1,dateadd(year,1,@dtBeginDate))

得到包含前月在內的一年時間

SET @dtBeginDate = getdate()

SET @dtEndDate = dateadd(month,-1,dateadd(year,1,@dtBeginDate))

高能預警

DATEPART ( datepart , date ) datepart函式中一週是週日到週六，而我們通常認為一週是週一到週日
 返回表示指定日期的指定日期部分的整數
本週第一天 (星期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()) 
得到上個月月末日期：
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())  
本月最後一天(當前為2011-03-31時會出錯) 選用：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())  
本月第一個星期一
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)--5．指定日期所在周的任意星期幾  
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)  
周內的第幾日  
select datepart(weekday,getdate()) as 周內的第幾日  
年內的第幾周  
select datepart(week,getdate()) as 年內的第幾周  
年內的第幾季  
select datepart(quarter,getdate()) as 年內的第幾季

DATEPART ( datepart , date ) datepart函式中一週是週日到週六，而我們通常認為一週是週一到週日

返回表示指定日期的指定日期部分的整數

本週第一天 (星期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())

得到上個月月末日期：

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())

本月最後一天(當前為2011-03-31時會出錯) 選用：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())

本月第一個星期一

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)--5．指定日期所在周的任意星期幾

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)

周內的第幾日

select datepart(weekday,getdate()) as 周內的第幾日

年內的第幾周

select datepart(week,getdate()) as 年內的第幾周

年內的第幾季

select datepart(quarter,getdate()) as 年內的第幾季

快速高效建立數字輔助表

--建立數字輔助表
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.jobbole.com/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.jobbole.com/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

--建立數字輔助表

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.jobbole.com/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.jobbole.com/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

練習：將下面表1每行字串轉化為表2格式

/*PlanDetailID  Description
1    課程詳細安排1,課程詳細安排1.1,課程詳細安排1.2,課程詳細安排1.3
2    課程詳細安排2,課程詳細安排2.1,課程詳細安排2.2
3    課程詳細安排3,課程詳細安排3.1,課程詳細安排3.2,課程詳細安排3.3,課程詳細安排3.4
4    課程詳細安排4
5    課程詳細安排5

轉化為：
PlanDetailID pos Description
1    1    課程詳細安排1
1    2    課程詳細安排1.1
1    3    課程詳細安排1.2
1    4    課程詳細安排1.3
2    1    課程詳細安排2
2    2    課程詳細安排2.1
2    3    課程詳細安排2.2
3    1    課程詳細安排3
3    2    課程詳細安排3.1
3    3    課程詳細安排3.2
3    4    課程詳細安排3.3
3    5    課程詳細安排3.4
4    1    課程詳細安排4
5    1    課程詳細安排5
*/

/*PlanDetailID Description

1 課程詳細安排1,課程詳細安排1.1,課程詳細安排1.2,課程詳細安排1.3

2 課程詳細安排2,課程詳細安排2.1,課程詳細安排2.2

3 課程詳細安排3,課程詳細安排3.1,課程詳細安排3.2,課程詳細安排3.3,課程詳細安排3.4

4 課程詳細安排4

5 課程詳細安排5

轉化為：

PlanDetailID pos Description

1 1 課程詳細安排1

1 2 課程詳細安排1.1

1 3 課程詳細安排1.2

1 4 課程詳細安排1.3

2 1 課程詳細安排2

2 2 課程詳細安排2.1

2 3 課程詳細安排2.2

3 1 課程詳細安排3

3 2 課程詳細安排3.1

3 3 課程詳細安排3.2

3 4 課程詳細安排3.3

3 5 課程詳細安排3.4

4 1 課程詳細安排4

5 1 課程詳細安排5

參考SQL：

--生成副本，按逗號的個數，n為逗號的位置（預設第一位為逗號）
SELECT  PlanDetailID ,
        Description ,
        n
FROM    dbo.T_PlanDetail
        INNER JOIN dbo.Nums ON n  LEN(Description) + 1   --若無AND，則表示按字元個數來生成行數
                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--將含有逗號時候的行輸出

--計算每一個字串的長度
SELECT  PlanDetailID ,
        SUBSTRING(Description, n, CHARINDEX(',', Description + ',', n) - n) AS element--元素的長度等於下一個逗號的位置減該元素的開始位置
FROM    dbo.T_PlanDetail
        INNER JOIN dbo.Nums ON n  LEN(Description) + 1   --若無AND，則表示按字元個數來生成行數
                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--將含有逗號時候的行輸出

--計算每個字串在陣列中的位置,按PlanDetailID 分割槽，按 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，則表示按字元個數來生成行數
                               AND SUBSTRING(',' + Description, n, 1) = ',' ;--將含有逗號時候的行輸出

--生成副本，按逗號的個數，n為逗號的位置（預設第一位為逗號）

SELECT PlanDetailID ,

Description ,

FROM dbo.T_PlanDetail

INNER JOIN dbo.Nums ON n LEN(Description) + 1 --若無AND，則表示按字元個數來生成行數

AND SUBSTRING(',' + Description, n, 1) = ',' ;--將含有逗號時候的行輸出

--計算每一個字串的長度

SELECT PlanDetailID ,

SUBSTRING(Description, n, CHARINDEX(',', Description + ',', n) - n) AS element--元素的長度等於下一個逗號的位置減該元素的開始位置

FROM dbo.T_PlanDetail

INNER JOIN dbo.Nums ON n LEN(Description) + 1 --若無AND，則表示按字元個數來生成行數

AND SUBSTRING(',' + Description, n, 1) = ',' ;--將含有逗號時候的行輸出

--計算每個字串在陣列中的位置,按PlanDetailID 分割槽，按 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，則表示按字元個數來生成行數

AND SUBSTRING(',' + Description, n, 1) = ',' ;--將含有逗號時候的行輸出

在sql server中經常有這樣的問題：
一個表採用了自動編號的列之後，由於測試了好多資料，自動編號已累計了上萬個。
現在正是要用這個表了，測試資料已經刪了，遺留下來的問題 就是在錄入新的資料，編號只會繼續增加，已使用過的但已刪除的編號就不能用了，
 誰知道如何解決此問題？

truncate命令不但會清除所有的資料，還會將IDENTITY的SEED的值恢復到原是值。

在sql server中經常有這樣的問題：

一個表採用了自動編號的列之後，由於測試了好多資料，自動編號已累計了上萬個。

現在正是要用這個表了，測試資料已經刪了，遺留下來的問題就是在錄入新的資料，編號只會繼續增加，已使用過的但已刪除的編號就不能用了，

誰知道如何解決此問題？

truncate命令不但會清除所有的資料，還會將IDENTITY的SEED的值恢復到原是值。

你真的懂函式嗎？
2018-03-11
函式
Myabtis動態SQL，你真的會了嗎？
2020-09-06
SQL
前端er，你真的會用 async 嗎？
2018-12-02
前端
stl中的sort函式，你真的瞭解嗎
2020-12-30
函式
常用函式彙總
2019-07-10
函式
【彙總】scapy 函式
2024-09-14
函式
Oracle函式彙總
2022-12-23
Oracle函式
string 函式彙總
2021-10-12
函式
你真的會用二分查詢嗎？
2019-03-03
你真的瞭解“SQL”嗎？《SQL優化最佳實踐》作者帶你重新瞭解SQL
2019-07-11
SQL優化
MySQL常用函式彙總
2020-10-24
MySql函式
開窗函式彙總
2024-08-29
函式
AA常用函式彙總
2024-08-04
函式
PHP字串函式彙總
2021-12-01
PHP字串函式
PHP 常用函式彙總
2023-02-19
PHP函式
Java中執行緒池，你真的會用嗎？
2018-10-31
Java執行緒
你真的會用PostGIS中的buffer緩衝嗎？
2020-11-12
Mysql 常用函式（1）- 常用函式彙總
2020-05-14
MySql函式
你真的會vue-router嗎？
2019-03-18
Vue
你真的會寫單例模式嗎——Java實現
2019-02-21
單例模式Java
恕我直言你可能真的不會java第8篇-函式式介面
2020-06-29
Java函式
MySQL視窗函式彙總
2024-09-27
MySql函式
PHP陣列函式彙總
2021-11-25
PHP陣列函式
陣列常用函式彙總
2021-10-12
陣列函式
每天加班的你，真的會工作嗎？
2019-10-22
Java內部類你真的會嗎？
2019-07-16
Java
堆排序你真的學會了嗎？
2021-07-25
排序
AsyncTask你真的用對了嗎？
2019-03-21
hitTest和pointInside方法你真的熟嗎？
2019-03-04
IDE
java中的Arrays這個工具類你真的會用嗎
2020-06-29
Java
Openmp Runtime 庫函式彙總（上）
2023-01-15
函式
輸出輸入函式彙總
2021-10-22
函式
你真的會搭建測試環境嗎？
2018-05-30
Flutter篇之你真的會使用Future嗎？
2019-07-19
Flutter
async await 你真的用對了嗎？
2020-11-20
AI
[程式設計] 你真的會寫函式註釋？這裡給你一個答案
2023-12-04
程式設計函式
php常用函式彙總.以備查.
2021-09-09
PHP函式
MySQL日期和時間函式彙總
2020-12-09
MySql函式
sql(oracle)資料處理實用總結開窗函式(over partition)使用
2018-06-05
SQLOracle函式

你真的會玩SQL嗎？實用函式方法彙總

相關文章