# SQL 語法集
###### tags: `SQL`

## # 符號的用法 ( 暫存 Table )
#### 說明
建立的暫存表會放在 tempdb

#### 用法
1 個 # 是「區域暫存資料表」
1 個 ## 是「全域暫存資料表」

#### 誰能用
區域暫存資料表只能在目前的工作階段中使用
全域暫存資料表可以在所有的工作階段中使用

#### 生命週期
目前自己測試，工作階段執行完是會自己從 tempdb Drop

---

## SELECT INTO 敘述句
#### SELECT INTO 語法 (SQL SELECT INTO Syntax)

SELECT table_column1, table_column2, table_column3...
INTO new_table_name [IN another_database]
FROM table_name;

其中 new_table_name 為欲新建的資料表名稱，該資料表會自動建立，且不可與已經存在的資料表名稱相同；而 another_database 為至外部資料庫的路徑。

#### SELECT INTO 敘述句用法 (Example)
例如，我們現在想備份顧客資料表 customers：

SELECT * INTO customers_backup FROM customers;

或者，我們想把它備份到 backup 資料庫中：

SELECT *
INTO customers_backup IN 'backup.mdb'
FROM customers;

我們也可以只複製資料表結構：

SELECT * INTO new_table_name FROM table_name WHERE 0=1;

MySQL 資料庫不支援 SELECT INTO，但你可以用 INSERT...SELECT 來達到同樣的功能。

[參考資料](https://www.fooish.com/sql/select-into.html)

---

## ROW_NUMBER() & OVER
#### ROW_NUMBER() OVER(PARTITION BY AAA ORDER BY BBB)

-- OVER子句內的 PARTITION BY 是指定 documentDate, substring (DocumentId,1,1) 欄位做分割，
-- 被分割的會自成一個群組，並以 DocumentId 欄位決定編號的排序。

以 Subject 排序進行編號的結果

以 DocumentID 排序進行編號的結果

---

## SET NOCOUNT ON
在執行每句T-SQL陳述式時，依據預設值，系統都會自動傳送此陳述式所影響到的資料列之筆數。

但若是要執行包含了多個陳述式的預存程序、觸發程序等，或是包含了迴圈等運算邏輯的陳述式，反而會產生大量的網路傳輸量。

> -- 建立資料表
CREATE TABLE count1
(cid int)
GO
-- 新增 100 筆資料列
DECLARE @cnt INT=1
WHILE @cnt<=100
BEGIN
INSERT count1 VALUES(@cnt)
SET @cnt +=1
END

因此，**建議在預存程序、觸發程序、使用者自訂函數等內或是執行T-SQL陳述式之前，加入設定SET NOCOUNT為ON**，調整為不要回傳所影響的資料列之筆數訊息。

減少了網路往返流量，就可以提昇其執行效能。

[參考資料](http://sharedderrick.blogspot.com/2010/06/set-nocount-on.html)

雖然不會回傳影響的資料列筆數，但如果想知道影響的資料列，還是能用 **@@ROWCOUNT** 來讀取

---

## Sql Server字串前面加上【N】的作用
#### 說明
在字串前面加上 N 代表存入資料庫時以 Unicode 格式儲存。
N'string' 表示string是個Unicode字串
[資料來源](https://www.itread01.com/p/1399893.html)

#### 加碼說明: SELECT資料時，資料內有特殊中文字時的解決方式
當資料庫裡有特殊的中文字，例如凃、喆等，這以下語法取不出欄位NAME = '凃' 的資料，該如何處理 ?
`SELECT * FROM [TABLENAME] WHERE [NAME] = '凃'`

在字串前加上大寫 N，指定字串格式為 Unicode 字串即可

`SELECT * FROM [TABLENAME] WHERE [NAME] = N'凃'`

> 結果如圖
> 

[資料來源](https://dotblogs.com.tw/chou/2009/05/19/8464)

---

## 宣告變數，修改變數的值

DECLARE @count int, @x int, @y nvarchar(10)

-- 檢查變數的初始值
SELECT [@count] = @count, [@x] = @x, [@y] = @y

-- 使用 SET 指派值
SET @count = 1

-- 使用 SELECT 指派值
SELECT @x = 0, @y = 'alexc'

-- 檢查變數的設定值
SELECT [@count] = @count, [@x] = @x, [@y] = @y

> 結果
> 

[資料參考](https://ithelp.ithome.com.tw/articles/10009411)

---

## LEN
傳回指定字串運算式的字元數，但尾端空格不算。

---

## 取得日期相關函式

### 取得目前的系統日期和時間
#### 語法

SELECT SYSDATETIME()  
    ,SYSDATETIMEOFFSET()  
    ,SYSUTCDATETIME()  
    ,CURRENT_TIMESTAMP  
    ,GETDATE()  
    ,GETUTCDATE();

#### 結果

SYSDATETIME()      2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()   2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP  2007-04-30 13:10:02.047
GETDATE()          2007-04-30 13:10:02.047
GETUTCDATE()       2007-04-30 20:10:02.047

### 取得目前的系統日期
#### 語法

SELECT CONVERT (date, SYSDATETIME())  
    ,CONVERT (date, SYSDATETIMEOFFSET())  
    ,CONVERT (date, SYSUTCDATETIME())  
    ,CONVERT (date, CURRENT_TIMESTAMP)  
    ,CONVERT (date, GETDATE())  
    ,CONVERT (date, GETUTCDATE());

#### 結果

SYSDATETIME()          2007-05-03  
SYSDATETIMEOFFSET()    2007-05-03  
SYSUTCDATETIME()       2007-05-04  
CURRENT_TIMESTAMP      2007-05-03  
GETDATE()              2007-05-03  
GETUTCDATE()           2007-05-04

### 取得目前的系統時間
#### 語法

SELECT CONVERT (time, SYSDATETIME())  
    ,CONVERT (time, SYSDATETIMEOFFSET())  
    ,CONVERT (time, SYSUTCDATETIME())  
    ,CONVERT (time, CURRENT_TIMESTAMP)  
    ,CONVERT (time, GETDATE())  
    ,CONVERT (time, GETUTCDATE());

#### 結果

SYSDATETIME()      13:18:45.3490361  
SYSDATETIMEOFFSET()13:18:45.3490361  
SYSUTCDATETIME()   20:18:45.3490361  
CURRENT_TIMESTAMP  13:18:45.3470000  
GETDATE()          13:18:45.3470000  
GETUTCDATE()       20:18:45.3470000

[資料來源](https://docs.microsoft.com/zh-tw/sql/t-sql/functions/getdate-transact-sql?view=sql-server-ver15)

---

## With 寫法

在SP寫的話 With前面需要+上分號(;)
`;With attendance AS`

With attendance AS
(
	Select WorkDay, Count(*) as DeclareCount
	From dbo.tblAttendanceSheet
	Where WorkDay between @StartDate and @EndDate and (WorkDayStatus IN (N'3')) AND (ENO IN (SELECT ENO FROM #ENOList))
), attendance1 AS
(
	Select WorkDay, Count(*) as DeclareCount
	From dbo.tblAttendanceSheet
	Where WorkDay between @StartDate and @EndDate and (WorkDayStatus IN (N'3')) AND (ENO IN (SELECT ENO FROM #ENOList))
), Result AS
(
	Select ad.WorkDay, ad.DeclareCount, ad1.DeclareCount
	From attendance ad
	left Join attendance1 ad1 ON ad.WorkDay = ad1.WorkDay
)

---

## Merge 寫法 ( 不完全對，還是要上網查 )

Merge tblAttendanceSheet as t
Using Result as s
ON t.workday = s.workday
When Matched Then
Update SET col1 = s.col1 ...
When Not Matched By target
Insert Into (cols) Values(cols)
When Not Matched By Source
Delete;

---

## PIVOT 資料轉置 ( 直變橫；橫變直是 UNPIVOT )

### 微軟說明
> 您可以使用 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。 PIVOT 會透過將唯一值從運算式中的某一資料行轉換為輸出中的多個資料行，來旋轉表格值運算式。 **而 PIVOT 會在最終輸出中需要它們的任何其餘資料行上執行彙總**。 UNPIVOT 執行的作業與 PIVOT 相反，它會將資料表值運算式的資料行旋轉成資料行值。

### 原本資料

### 轉置後資料

### 語法

Select * From (
    Select  memberData.MemberApplyId, memberData.Title, memberData.Value, memberApply.TicketId
    From tblMemberApply memberApply  
    Left Join tblMemberApplyFormData memberData on memberApply.id = memberData.MemberApplyId
    Where memberApply.MemberId in ( Select Element FROM dbo.Splits('3,4',',') )
    And memberApply.IsConfirm = 1
    And memberApply.EventId = 19
) t 
PIVOT(
    MAX(t.Value) for t.Title In ([姓名],[電子郵件],[手機])
) p;

### 語法解讀 & 自我解讀
* 用原本的 Table 去對應 PIVOT 處理後的表格
* MAX() => PIVOT 強制使用彙總函式，為了最終只取一個結果，假設相同的 MemberApplyId 有兩筆 Title 為姓名的資料，才不會不知道該怎麼取

---