TSQL - #Table、CTE、@Table
暫存表
- 不論當前是在使用哪個 Database，都是建在 Tempdb。
- 還是會有實際磁碟 IO
- 消失時機
- 自行刪除
- User 斷線
※ ##全區暫存 (通常只有管理員可建) : 用來給大家使用，但會消失，故很少使用這建法。
CREATE TABLE #MyUser --#MyUser 代表暫存
(
Id INT,
Name NCHAR(5)
)
Go
INSERT INTO #MyUser VALUES(1,'Hank')
INSERT INTO #MyUser VALUES(2,'Omar')
INSERT INTO #MyUser VALUES(3,'Shan')
UPDATE #MyUser SET Name=RTRIM('Hank ')+'DD' WHERE Id=2 --RTRIM 移除尾端空格
SELECT * FROM #MyUser
變數表
- 在記憶體中
- 快
- 使用一次就消失
DECLARE @MyUser TABLE
(
Id INT,
Name NCHAR(5)
)
INSERT INTO @MyUser VALUES(1,'Hank')
INSERT INTO @MyUser VALUES(2,'Omar')
INSERT INTO @MyUser VALUES(3,'Shan')
UPDATE @MyUser SET Name=RTRIM('Omar ')+'DD' WHERE Id=2
SELECT * FROM @MyUser SET
CTE (Common Table Expression)
- 暫存在記憶體
- 必須馬上用
- 可連續用
- 一次性的 View 和變數表很像
- 可大幅降低子查詢的使用
- 可以發生遞迴 (次數預設 100 次 )
WITH TT
AS
(
SELECT Id, Name, Price FROM Production
)
SELECT Name, Price FROM TT
WITH TT
AS
(
SELECT Id, Name, Price FROM Production
),
UU
AS
(
SELECT * FROM TT
)
SELECT Name, Price FROM UU
WITH TT
AS
(
SELECT B.Id
FROM Orders AS A JOIN OrdersDetail AS B ON A.OrderId=B.OrderId
WHERE A.OrderDate>='2002-1-1' AND A.OrderDate<'2003-1-1'
)
SELECT * FROM ProductionInfo
WHERE ProductionId NOT IN(SELECT ProductionId FROM TT)
--CTE的遞迴
--SELECT * FROM EE OPTION(MAXRECURSION 1) <---指定遞迴次數 1 次，預設是 100，最大是 32767
SELECT EmployeeId,Name,NickName,Title,Supervisor
FROM Employee
WITH EE(EmployeeId,Name,NickName,Title,Supervisor,Layer)
AS
(
SELECT EmployeeId,Name,NickName,Title,Supervisor,1
FROM Employee
WHERE Supervisor IS NULL
UNION ALL
SELECT A.EmployeeId,A.Name,A.NickName,A.Title,EE.Layer+1
FROM Employee AS A JOIN EE ON A.Supervisor=EE.EmployeeId
)
SELECT * FROM EE
0 意見