SQL - Note

SQL Note

紀錄一些碰到的資料庫 SQL 中比較特別或怕會忘記的語法

DECLARE 宣告變數

DECLARE @CustomerID int = 775
SELECT OrderID, CustomerID, OrderDate, Quantity, Amount
FROM dbo.Orders
WHERE CustomerID = @CustomerID;

時間選取函數

假設 Table 名稱為：Book_Lend_Record

ID	BookName	LendTime
1	哈利波特	2023-07-04 00:00:00
2	波西傑克森	2023-07-05 03:00:00

• YEAR (dateTime)
  • 下例的回傳值會是 2023

    SELECT YEAR (LendTime)
    FROM Book_Lend_Record
    WHERE BookName = '哈利波特'

• DATENAME (datepart , dateTime)
  • 回傳指定 dateTime 中的 datepart
  • datapart 可以是：year、month、quarter、day、week、hour、minute、second…
  • 下例的回傳值會是 2023

    SELECT DATENAME (year, LendTime) 
    FROM Book_Lend_Record
    WHERE BookName = '哈利波特' 

• 轉換時間的表達格式
  • ex: 想輸出 2023/07/05

    SELECT convert(varchar, getdate(), 111) - yyyy/mm/dd

• 將時間字串轉換為 DATETIME 格式
  • ex: 想輸出 2023-07-07 00:00:00.000

    CONVERT(DATETIME, '2023/07/05', 121)

• FORMAT() 字串函數
  • 傳回以指定格式與選擇性文化特性所格式化的值
  • ex: 想輸出 2023/7/5 (月份日期前不補 0)

    SELECT FORMAT(BD.BOOK_BOUGHT_DATE, 'd') AS '購書日期'
    FROM BOOK_DATA AS BD 

金額顯示千分位逗點

• CONVERT(VARCHAR(12), CONVERT(MONEY, '1234567'), 1) 完後會輸出 11,450.00，但因最後不需要小數點，故再利用 REPLACE 處理
• 會輸出 11,450 元

  SELECT REPLACE(CONVERT(VARCHAR(12), CONVERT(MONEY, BD.BOOK_AMOUNT), 1), '.00', ' 元') AS '購書金額'
  FROM BOOK_DATA AS BD 

字串串接

• 將多筆資料合併為一筆複合字串
  • SELECT 的欄位之間不用逗號，改為 '<欲作為中間連間的字符>'
  • 輸出為 <USER_ID>-<USER_CNAME>(USER_ENAME)

    SELECT M.USER_ID + '-' + M.USER_CNAME + '(' + M.USER_ENAME + ')' AS '借閱人'
    FROM MEMBER_M AS M

CASE..WHEN..END

• 用作邏輯判斷
  假設 Table 名稱為：Grading_Survey

ID	Name	Grade
1	LAVI	1
2	Momi	2

SELECT Name, CASE Grade
	when 1 then 'LIKE'
	when 2 then 'LOVE'
END
FROM Grading_Survey;

• 會輸出

Name	Grade
LAVI	LIKE
Momi	LOVE

COUNT()

• 特別用法，結合 CASE..WHEN..END
• 同時 COUNT 同一欄位的多個條件

  COUNT(CASE WHEN <Condiction> THEN 1 END),
  COUNT(CASE WHEN <Condiction> THEN 1 END),
  COUNT(CASE WHEN <Condiction> THEN 1 END),
  COUNT(CASE WHEN <Condiction> THEN 1 END)

Sorting and Filtering Data

TOP

• 排序取前幾名
• TOP 預設判斷排序方式為由小到大
  假設 Table 名稱為：Bread_Price

ID	BreadName	Price
1	紅豆麵包	25
2	藍莓奶酪餅	45
3	蜂蜜吐司	30

SELECT TOP (2) ID, BreadName, Price
FROM Bread_Price
GROUP BY ID, BreadName, Price
ORDER BY Price;

• 會輸出

ID	BreadName	Price
1	紅豆麵包	25
3	蜂蜜吐司	30

Window Ranking & Offset & Aggregate Functions

RANK()

• 一種 Window Ranking 函數 (視窗函數)
• 預設依照 ORDER BY 排序，若有同分則同名，但下一分段會變成當前名次 + 同名人數

  RANK (integer_expression) 
   OVER ( [ <PARTITION BY ...> ] < ORDER BY ... > )  

DENSE_RANK()

• 一種 Window Ranking 函數 (視窗函數)
• 預設依照 ORDER BY 排序，若有同分則同名，但下一分段會接續當前名次

  DENSE_RANK(integer_expression) 
   OVER ( [ <PARTITION BY ...> ] < ORDER BY ... > )  

NTILE(n)

• 平均等分成 n 等份

  NTILE(integer_expression) OVER ( [ <PARTITION BY ...> ] < ORDER BY ... > )

ROW_NUMBER ()

ROW_NUMBER () OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

LAG() & LEAD()

• LAG()
  • 利用 PARTITION BY 和 ORDER BY 進行分割跟排序後，排列好的資料集，列出前一筆的該值
• LEAD()
  • 利用 PARTITION BY 和 ORDER BY 進行分割跟排序後，排列好的資料集，列出後一筆的該值

Queries with PIVOT

• 使用 PIVOT 扭轉資料，由直列轉為橫向資料

  SELECT BOOK_CLASS_ID, [<要變成欄位名稱的行值1>], [<要變成欄位名稱的行值2>], 
      [<要變成欄位名稱的行值3>], [<要變成欄位名稱的行值4>]
  FROM ( SELECT BOOK_CLASS_ID, <欄位Y>
  	   FROM BOOK_DATA AS BD 
  	 ) AS SRCTAB
  PIVOT( <Aggregate Function> FOR <欄位Y> IN ([<要變成欄位名稱的行值1>], [<要變成欄位名稱的行值2>], 
      [<要變成欄位名稱的行值3>], [<要變成欄位名稱的行值4>])
       ) AS PIVOTTAB

• 動態 PIVOT 使用 EXEC sp_executeSQL

  DECLARE @ColumnGroup NVARCHAR(200) = '<要 SELECT 的欄位>,
  	[<要變成欄位名稱的行值1>], [<要變成欄位名稱的行值2>], 
      [<要變成欄位名稱的行值3>], [<要變成欄位名稱的行值4>]'
  DECLARE @PivotSQL NVARCHAR(MAX)
  
  SET @PivotSQL = N'
  	SELECT ' + @ColumnGroup + N' 
  	FROM (
  			SELECT BOOK_CLASS_ID, <欄位Y>
  	        FROM BOOK_DATA AS BD 
  		 ) AS SRCTAB
  	PIVOT(
  			<Aggregate Function> FOR <欄位Y> IN ([<要變成欄位名稱的行值1>], [<要變成欄位名稱的行值2>], 
              [<要變成欄位名稱的行值3>], [<要變成欄位名稱的行值4>])
  		 ) AS PIVOTTAB
  	ORDER BY BOOK_CLASS_ID;
  	'
  EXEC sp_executeSQL @PivotSQL

Programming with Transact-SQL

• @@ROWCOUNT
  • 被影響或讀取的資料列數
  • 可利用此方式找出所搜尋之資料共有多少列

    SELECT DISTINCT <column> FROM <table> 
    SELECT @@ROWCOUNT

Reference

• 各式各樣的 GETDATE() 時間格式轉換 CONVERT
• FORMAT() 字串函數
  傳回以指定格式與選擇性文化特性所格式化的值
• Convert String to Datetime
• CASE 語法 (SQL CASE Syntax)
• 將多筆資料合併為一筆複合字串
• 金額顯示千分位逗點
• Sorting and Filtering Data
  • sql server 按年、季度、月份、周統計訂單銷量 金額 top10 均價
• Window Ranking, Offset & Aggregate Functions
  • 透過 RANK() 與 DENSE_RANK() 進行資料排序
  • 程式設計師之家
  • SQL LEAD() LAG() 函數怎麼寫？5 種用法教學
  • DATENAME (Transact-SQL)
  • 同時 COUNT 同一欄位的多個條件
• Queries with PIVOT
  • PIVOT
  • 動態串接 + PIVOT
  • FOR XML PATH(‘’),TYPE).value(‘.’, ‘NVARCHAR(max)’).value(‘.’%20’nvarchar(max)’)%201%201%20’’))
• Programming with T-SQL
  • @@ROWCOUNT (Transact-SQL)
  • 使用 FOR XML PATH 、CTE、PIVOT 產生動態欄位統計表