SQL Server 2005 Beta 2 Transact-SQL 增强功能(二)

您希望为 T1 中的每个行调用 fn_scalar_min_max。您可以按如下方式编写 CROSS APPLY 查询：

SELECT *
FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M

以下为结果集：

col1 col2 mn mx
----------- ----------- ----------- -----------
10 20 10 20
20 10 10 20
NULL 30 30 30
40 NULL 40 40
50 50 50 50

如果该表值函数为特定的外部行返回多个行，则该外部行被多次返回。考虑在本文前面的递归查询和常见表表达式一节中使用的 Employees 表（“雇员组织结构图”方案）。在同一数据库中，您还创建了以下 Departments 表：

CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
SET NOCOUNT ON
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)

大多数部门都具有一个与 Employees 表中的某个雇员相对应的经理 ID，但是像 Gardening 部门一样，有些部门可能没有经理。请注意，Employees 表中的经理必然管理某个部门。以下表值函数接受雇员 ID 作为参数，并且返回该雇员及其所有级别的所有下属：

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

要为每个部门的经理返回所有级别的所有下属，请使用以下查询：

SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

以下为结果集：

deptid deptname deptmgrid empid empname mgrid lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1

这里需要注意两个事情。第一，Departments 中的每个行都被复制与从 fn_getsubtree 中为该部门的经理返回的行数一样多的次数。第二，Gardening 部门不会出现在结果中，因为 fn_getsubtree 为其返回空集。

CROSS APPLY 运算符的另一个实际运用可以满足以下常见请求：为每个组返回 n 行。例如，以下函数返回给定客户的请求数量的最新定单：

USE AdventureWorks
GO
CREATE FUNCTION fn_getnorders(@custid AS INT, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = @custid
ORDER BY OrderDate DESC
GO

使用 CROSS APPLY 运算符，可以通过下面的简单查询获得每个客户的两个最新定单：

SELECT O.*
FROM Sales.Customer AS C
CROSS APPLY fn_getnorders(C.CustomerID, 2) AS O

有关 TOP 增强功能的详细信息，请参阅下文中的&ldq