【Microsoft SQL Server 2008 技術內幕：T-SQL語言基礎】二、查詢篇

sql2008 t-sql

[b][size=large]單表查詢[/size][/b]

[b]TOP選項[/b]
1. (結合order by)返回的是有固定順序的遊標
2. 可以使用[i]percent[/i]關鍵字
3. 附加屬性[i]tiebreaker[/i]：
在沒有給定所有列的order by次序時，top語句的返回列可能是不確定的，sql server將按照物理順序返回結果。
[i]tiebreaker[/i]是一個用以確定唯一結果順序的field或field列表。
4. [b]WITH TIES[/b]選項 (相對於tiebreaker)
除了使用tiebreaker，還可以使用該選項返回具有所有相同結果的行。
例如 select top(3) field1, field2, field3 from xtable，可能會返回a,b,c行或a,b,d行
而select top(3) with ties field1, field2, field3 from xtable將返回所有的可能結果行: a,b,c,d

[b]OVER子句[/b]
over子句為行定義一個視窗(window)，以進行視窗函式(window function, 又分為[i]ranking[/i]和[i]aggregate[/i])計算。
傳統的聚合(aggregate)函式以group by查詢作為操作上下文，每個每組僅返回一個值
開窗聚合函式使用over子句為上下文，沒有資料分組，可以在返回記錄中同時使用from, where, group by中的可用欄位。
Ranking function: ROW_NUMBER, RANK, DENSE_RANK, NTITLE

select ROW_NUMBER() over(order by SalesOrderID) as RowNumber
	, RANK() over (order by SalesOrderID) as Rank  
	, SalesOrderID
	, ProductID
	, OrderQty
	, SUM(OrderQty) over(partition by SalesOrderID) as Total
from SalesLT.SalesOrderDetail as A

[b][size=large]查詢後設資料[/size][/b]

Sql server提供了一系列檢視，用於獲取資料庫物件的後設資料資訊的工具：
目錄檢視 (Booksonline: catalog views): 提供資料庫中各個物件的資訊
資訊架構檢視 (Booksonline: INFORMATION_SCHEMA views): 基於ANSI SQL標準的形式提供後設資料資訊，不包含Sql server所特有的內容
系統儲存過程和函式 (Booksonline: system stored procedures, system functions):

[b][size=large]聯接查詢[/size][/b]

1. 左聯應用中非常有用的數字序列表，可以用來構造一個連續的結果集作為左聯基礎：

set nocount on
if OBJECT_ID('dbo.Nums', 'U') is not null drop table dbo.Nums
create table dbo.Nums(n int not null primary key)

declare @i as int = 1  -- SQL 2008 新語法
begin tran
  while @i <=10000
  begin
    insert into dbo.Nums values(@i)
    set @i = @i +1
  end
commit tran
set nocount off

select DATEADD(day, n-1, '20060101') as orderdate
from dbo.Nums
where n <= DATEDIFF(day, '20060106', '20081231') + 1
order by orderdate

2. 左聯的抵消
[list]
[*]如果在左聯的where子句中使用了非保留列的表示式作為條件，那麼左聯效果會被抵消，結果集會變成內聯的效果。
[*]因為該非保留列的表示式永遠為: NULL<運算子><值>, 其值永遠為unkown
[/list]
3. 多表連線中使用外聯容易出現的邏輯錯誤。下邊是2.3點的例子

-- 1. 錯誤邏輯的例子
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.Customer as C
	left join SalesLT.SalesOrderHeader as O
		on C.CustomerID = O.CustomerID
	join SalesLT.SalesOrderDetail as OD
		on O.SalesOrderID = OD.SalesOrderID	 -- 使用了非保留列作條件，將會抵消左聯

-- 2. 正解1，連續左聯
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.Customer as C
	left join SalesLT.SalesOrderHeader as O
		on C.CustomerID = O.CustomerID
	left join SalesLT.SalesOrderDetail as OD -- 繼續左聯
		on O.SalesOrderID = OD.SalesOrderID	

-- 3. 正解2，先內聯再左聯
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.SalesOrderHeader as O		
	join SalesLT.SalesOrderDetail as OD		 -- 先內聯
		on O.SalesOrderID = OD.SalesOrderID			
	right join SalesLT.Customer as C         -- 再右聯
		on O.CustomerID = C.CustomerID

-- 4. 正解3，類似正解2，使用括號，進行邏輯巢狀即可，程式碼省略

4. 表關聯是的運算順序為自左至右
5. 在外聯查詢中使用count應注意它會將外部行業納入統計，因為它只根據行數進行統計

[b][size=large]子查詢[/size][/b]

[list]
[*]分為獨立子查詢(self-contained subquery)，相關子查詢(correlated subquery，子查詢中引用了外部查詢的表)。
[*]查詢結果可以返回單值(標量 scalar)，多值(multi-valued)或表(table)
[/list]

-- 返回前一條記錄的子查詢	
-- 注意，這裡是最簡單的一個例子，效率會比較低
-- 可以把條件轉移到where子句以提高效率
-- 或者使用錯位聯接，效率應該更高	
select SalesOrderID, OrderDate
	, (select MAX(O2.SalesOrderID)
	   from SalesLT.SalesOrderHeader as O2
	   where O2.SalesOrderID < O1.SalesOrderID) as prevorderid
from SalesLT.SalesOrderHeader as O1

-- 連續聚合 (running aggregate)
-- 通常使用連續聚合時應該至少有一個時間這樣型別的列來表達時間連續的概念
select orderyear, qty
	, (select SUM(O2.qty)
	   from SalesLT.OrderTotalByYear as O2
	   where O2.orderyear <= O1.orderyear) as runqty
from SalesLT.OrderTotalByYear as O1
order by orderyear

-- 行為不當的子查詢1，返回沒下過單的客戶
-- 如果子查詢的返回有null,則該查詢結果永遠為空，因為where子句中的條件結果為unknown
select CustomerID, CompanyName
from SalesLT.Customer as C
where CustomerID not in (select CustomerID 
						 from SalesLT.SalesOrderHeader) -- 沒有考慮CustomerID為空的情況
-- 最佳實踐： 1.必要時設定field為not null。2.時刻注意SQL的三值邏輯中null的處理


-- 行為不當的子查詢2，相近名稱造成的子查詢混淆
-- 造成這個錯誤一方面由於名字相近
-- 另一方面是由於sql server的搜尋策略：當內查詢不存在指定的field時，繼續搜尋外部查詢
-- 這種行為無意中將一個獨立子查詢轉換成了相關子查詢

create table SalesLT.MyShippers (
shipper_id int not null primary key,
companyname nvarchar(40) not null
)
create table SalesLT.Orders (
  custid int not null primary key,
  shipperid int not null
)

-- 返回把訂單發貨給43號客戶的發貨人
select shipper_id, companyname
from SalesLT.MyShippers
where shipper_id in ( select shipper_id     -- 注意這裡不是 shipperid,
					  from SalesLT.Orders
					  where custid = 43)

-- 最佳實踐：
-- 1. 長遠角度應該考慮統一、一致的命名方案
-- 2. 短期角度則應該在子查詢的field前指定來源表