MS SQL 學習紀錄-2

Batch Separators 批次處理分隔符號

• SQL server 用戶端工具 (如 SSMS) 將命令發送到資料庫引擎中的集合就稱為批次處理
• 若手動執行 T-SQL 指令碼 (例如在查詢編輯器中)，則可以選擇是否將 T-SQL 指令檔中的所有指令作為一個批次處理發送，也可以選擇在某些程式碼片段之間插入分隔符號，這個分隔符號就是 GO 指令
• GO 指令只能用於 SSMS 的「查詢視窗」和命令列工具的 SQLCMD.exe
• 在一個「查詢視窗」中，GO 指令能夠製造出不同的邏輯批次作業空間
  SQL Server 執行 T-SQL 指令到 GO 指令時，代表完成一個批次作業，在 GO 指令之後的 T-SQL 指令代表另一個批次作業的開始
  批次作業結束時會釋放批次作業中已定義的變數
• 多數的 DDL 指令必須描述在批次作業第一個被執行的位置，利用 GO 指令在「查詢視窗」中邏輯分割出不同的批次作業空間，將可滿足 DDL 指令執行環境

注意事項：

GO 指令用於命令列工具的 SQLCMD.exe，代表要求「執行」前面已輸入的 T-SQL 指令。
GO 指令不是標準 ANSI-SQL 所定義的陳述式，只能用於微軟 SQL Server。
GO 指令並不屬於 T-SQL，所以不能寫入前端的程式碼中。

排序資料

使用資料庫產生報表時我們常常會需要由大至小或由小至大的排序方便我們來作業，而再來介紹的就是排序資料order by。

• ORDER BY 子句用來控制 SELECT 傳回的結果集的資料排序

• 若沒有 ORDER BY 子句，SELECT 傳回的結果集無法保證一定的順序，簡單地說，毫無順序可言

-- 未加 ORDER BY 不保證傳回的順序
use tsql2;
go
select lastname, empid
from hr.employees;
go
select empid, lastname, salary
from hr.employees;
go

• 若排序的欄值有 NULL 空值，則會匯集在一起 (置頂)

• 出現在 ORDER BY 子句中的可以是：

  • 資料行名稱 (欄位名稱、欄名)
  • 資料行別名 (欄別名)
  • 在 SELECT 選取清單中的資料行順位 (由左至右，從 1 號開始)
  • 未出現在 SELECT 選取清單中的資料行，也可以出現在 ORDER BY， * 若 SELECT 指定了 DISTINCT，則只有在 DISTINCT 後的欄名，才能用於 ORDER BY 子句
  • 運算式

• 欄值預設為升序排序，也可以在欄名後明確指定 ASC (可省略)

• 若欄值需為降序排序，則必須在欄名後明確指定 DESC

USE TSQL2;
GO
SELECT hiredate, firstname, lastname
FROM HR.Employees
ORDER BY hiredate DESC, lastname ASC;

• 中文字排序則依「定序」COLLATION 決定，有筆劃、注音等

練習 ORDER BY 子句的排序資料

將上一個 Exercises 之結果，將「出貨國家」以升序排序，「距出貨日天數(10日內)」以降序排序

select	OrderID 訂單編號,
		CustID 客戶編號,
		ShipCountry 出貨國家,
		month(orderdate) 訂單月份,
		OrderDate 訂單日期,
		datediff(d, orderdate, shippeddate) [距出貨日天數(10日內)]
from sales.orders
where month(orderdate) = 9 
		and ShipCountry IN (N'USA', N'UK')
		and datediff(d, orderdate, shippeddate) <= 10
order by 出貨國家 asc, [距出貨日天數(10日內)] desc

---

限制結果集的資料列

• 有兩種方法可以限制結果集的資料列：
  • TOP (N) 或 TOP (N) PERCENT + WITH TIES
    • 只能用在 MS-SQL Server
• OFFSET-FETCH
  • ANSI SQL 的標準

使用 TOP 子句限制資料列

• TOP 子句是用於 SELECT 和選取清單之間，一般都會搭配 ORDER BY 子句

• 可傳回結果集的：

  • 前段資料 (升序排序 ASC)
  • 後段資料 (降序排序 DESC)
  • 唯獨不能傳回結果集的中段資料

• 基本語法：

SELECT TOP (N) 選取清單
FROM 資料表來源
WHERE 搜尋條件
ORDER BY 排序清單;

• 相關的用法有：

1. 可用來限制傳回筆數

-- 傳回最後 5 筆訂單資料
SELECT TOP (5) orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

2. TOP + WITH TIES (平手的意思)

-- 傳回最後 5 筆訂單資料以及同一時間的訂單
SELECT TOP (5) WITH TIES orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

3. 限制百分比的筆數，但筆數都是固定的，也可以加上 WITH TIES

SELECT TOP (10) PERCENT orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

4. 隨機抽樣 (使用 newid())，限制百分比的筆數，但筆數都是固定的，也可以加上 WITH TIES，若想傳回不固定筆數，則可改用 TABLESAMPLE

-- 做法一
SELECT TOP (10) PERCENT orderid, custid, orderdate
FROM Sales.Orders
ORDER BY newid(), orderdate DESC;

-- 做法二
SELECT orderid, custid, orderdate
FROM Sales.Orders TABLESAMPLE SYSTEM (10 PERCENT )
ORDER BY orderdate DESC;

注意事項：

TOP (N) 必須是正整數，不可以為負整數
TOP (N) 不是標準 ANSI-SQL 語法，不建議使用，可使用 OFFSET-FETCH
TOP (N) 不能傳回結果集的中段資料
TABLESAMPLE SYSTEM 可省略 SYSTEM 關鍵字
TABLESAMPLE 支援 PERCENT 和 ROWS，例如 TABLESAMPLE (10)， PERCENT 是預設值

使用 OFFSET-FETCH 子句限制資料列

• 為 ORDER BY 子句延伸子句，所以需接在 ORDER BY 子句之後

• 自 SQL Server 2012 開始支援，依據 draft ANSI