使用 APPLY 運算子可以為實現查詢操作的外部表運算式返回的每個行調用表值函數。表值函數作為右輸入，外部表運算式作為左輸入。通過對右輸入求值來獲得左輸入每一行的計算結果，生成的行被組合起來作為最終輸出。APPLY 運算子生成的列的清單是左輸入中的列集，後跟右輸入返回的列的清單。
APPLY 有兩種形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 僅返回外部表中通過表值函數生成結果集的行。OUTER APPLY 既返回生成結果集的行，也返回不生成結果集的行，其中表值函數生成的列中的值為 Null。
--以上是SQLServer 2005説明中的講解,下面還是看個例子吧
-- apply運算子的主要用途就是和表值函數配合,用來替代SQLServer 2000中的游標
--Create Employees table and insert values
--員工表共四列員工id 部門主管id 員工姓名傭金
CREATE TABLE Employees
(
empid int NOT Null,
mgrid int Null,
empname Varchar(25) NOT Null,
salary money NOT Null,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)
GO
INSERT INTO Employees VALUES(1 , Null, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
--Create Departments table and insert values
--部門表共散列部門id 部門名稱外鍵部門主管id
CREATE TABLE Departments
(
deptid INT NOT Null PRIMARY KEY,
deptname VARCHAR(25) NOT Null,
deptmgrid INT Null REFERENCES Employees
)
GO
INSERT INTO Departments VALUES(1, 'HR', 2)
INSERT INTO Departments VALUES(2, 'Marketing', 7)
INSERT INTO Departments VALUES(3, 'Finance', 8)
INSERT INTO Departments VALUES(4, 'R&D', 9)
INSERT INTO Departments VALUES(5, 'Training', 4)
INSERT INTO Departments VALUES(6, 'Gardening', Null)
Go
select * from employees
select * from Departments
結果:
--表值函數根據部門主管id 查詢出該部門主管下屬員工
--with是CTE語法,不了解的先查詢SQLServer 2005説明
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid INT NOT Null,
empname VARCHAR(25) NOT Null,
mgrid INT Null,
lvl INT NOT Null
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
JOIN employees_subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO
-- 根據上面的表值函數我們可以很方便的查詢出某個部門主管的下屬都是誰
--但是,如果查詢出所有部門主管的下屬就麻煩了,需要使用游標
--定義表變數臨時存放資料
declare @tem table(
empid int,
mgrid int,
empname Varchar(25),
lvl int,
deptid INT,
deptname VARCHAR(25),
deptmgrid INT
)
DECLARE @ids int
DECLARE test_cursor CURSOR FOR
select deptmgrid FROM Departments
OPEN test_cursor
FETCH NEXT FROM test_cursor
INTO @ids
WHILE @@FETCH_STATUS = 0
begin
insert into @tem select empid, mgrid, empname, lvl, deptid, deptname, deptmgrid from dbo.fn_getsubtree(@ids) left join Departments on deptmgrid=@ids
FETCH NEXT FROM test_cursor
INTO @ids
end
CLOSE test_cursor
DEALLOCATE test_cursor
select * from @tem
結果:
--游標效率太差,萬不得已不要使用,SQLServer2005的apply運算子可以替代它
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
SELECT *
FROM Departments AS D
OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST
結果:
===================================================
最後一行資料體現出CROSS APPLY和OUTER APPLY的不同,這有點類似Inner join和left join的區別.
好吧,我承認我是標題党,apply配合表值函數的用法,只能替代只進游標,但是這種方法效率要比游標好的多.
全站熱搜
留言列表
