作者: Jason

http://kkbruce.blogspot.com/2011/01/t-sql-common-table-expression-cte.html

 

重點筆記：

• 可以把CTE當成”Temp View”
• 如果在Store Procedure使用CTE，會造成每次執行都會re-compiler，效能低落
• 資料集未以Table存放於Database時，適合用CTE
• View通常用來分解大型查詢，如果只用一次，那View不是好辦法
• 子查詢只能從所在的”陳述式”存取，如果有多個相同的子查詢，必須含重覆程式碼，難閱讀及維護
• CTE能在同批次下，重覆使用(像View一樣)
• CTE只能用於其後第一個陳述式(Select, Insert, Delete, Update)，此陳述式可多次取用CTE
• CTE後面可接另一個CTE，使用逗號(,)

 

http://www.flag.com.tw/book/cento-5105.asp?bokno=FS232&id=419

SQL – 使用 一般資料表運算式 CTE (Common Table Expression)

早期可以透過暫存資料 表(# 或 ##)或者 table 來存放資料，現在也可以使用 一般資料表運算式 CTE (Common Table Expression) 來暫存資料。雖然 CTE 的查詢是使用 Select 查詢，但是依照 MSDN 的說明，仍有一些限制，那就是在 CTE 中不可以使用以下的子句：

1.COMPUTE 或 COMPUTE BY
2.ORDER BY (除非指定了 TOP 子句)
3.INTO
4.含有查詢提示的 OPTION 子句
5.FOR XML
6.FOR BROWSE

以下就來實作一些 CTE 的範例…

範例一：單一的 CTE 用法

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
)

Select *
From OrdersTable

範例二：兩個 CTE 作 Join

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
),
OrderDetailsTable as
(
Select OrderID as ‘訂單ID’, ProductID as ‘產品ID’, UnitPrice as ‘價格’
from dbo.[Order Details]
)

Select *
From OrdersTable A inner join OrderDetailsTable B
on A.訂單ID = B.訂單ID

範例三：CTE 遞迴 (參考：黑暗大的部落格文章)

資料清單

Code：

–如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
–Recursive CTE分為兩個部分, 第一部分為Anchor Member
–指不會被遞迴呼叫到的部分
SELECT 部門名稱, 直屬部門, 0, CONVERT(nvarchar(128), 部門ID)
FROM dbo.公司組織 WHERE 直屬部門=N’ROOT’

UNION ALL

–UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
–直到無任何查詢結果為止
SELECT P.部門名稱, P.直屬部門, B.層級+1,
CONVERT(nvarchar(128), B.排序欄位 + ‘-‘ + CONVERT(nvarchar(128), P.部門ID))
FROM dbo.公司組織 P, 公司組織結果 B
WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE(‘       ‘, 層級) + 部門名稱) as ‘部門名稱’, 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位

另一個 CTE 遞迴範例 (參考：Journey to SQL Authority with Pinal Dave)

Code：

USE AdventureWorks
GO
WITH Emp_CTE AS (
–找出 ManagerID 為 NULL 的資料當作 Anchor Member
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL

UNION ALL

–之後以 Anchor Member 為依據遞迴查詢
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

 

原文網址

--Add DB Link Server
EXEC sp_addlinkedserver
@server = 'F6', --Server Name
@srvproduct = 'MS SQL',
@datasrc = '192.168.1.1' , --Server IP
@provider = 'SQLNCLI'

--Remove DB Link Server
EXEC sp_dropserver
@server = 'F6' --Server Name

--Add Login User and Password
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'F6' , --Server Name
@useself = 'false' ,
@locallogin = NULL ,
@rmtuser = 'sa' , --User
@rmtpassword = 'sasa' --Password
--Add DB Link Server
EXEC sp_addlinkedserver
@server = 'F6', --Server Name
@srvproduct = 'MS SQL',
@datasrc = '192.168.1.1' , --Server IP
@provider = 'SQLNCLI'

--Remove DB Link Server
EXEC sp_dropserver
@server = 'F6' --Server Name

--Add Login User and Password
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'F6' , --Server Name
@useself = 'false' ,
@locallogin = NULL ,
@rmtuser = 'sa' , --User
@rmtpassword = 'sasa' --Password

--Query DB Link Server
select * from sys.servers
--Query DB Link Server
select * from sys.servers

--Query Login User
select * from sys.linked_logins
--Query Login User
select * from sys.linked_logins

--Query DB Link Data
select * from [F6].[database].[dbo].[table]
--select * from [server name].[database name].[owner name].[table name]

--Query DB Link Data
select * from [F6].[database].[dbo].[table]
--select * from [server name].[database name].[owner name].[table name]

SELECT ‘1’AS [column_1] into [T2] from [T1]

以前一直都沒有這樣用，我只會笨笨的先CREAT TABLE再

INSERT INTO [T2] (column_1) SELECT column_1 FROM  [T1]

http://www.dotblogs.com.tw/dc690216/archive/2010/02/02/13440.aspx