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
SELECT * FROM Departments AS D OUTER 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 6 Gardening NULL NULL NULL NULL NULL
返回页首 相关子查询中的表值函数 在 SQL Server 2000 中，不能在相关子查询内部引用表值函数。与提供 APPLY 关系运算符一道，该限制在 SQL Server 2005 Beta 2 中被移除。现在，在子查询内部，可以向表值函数提供外部查询中的列作为参数。例如，如果您希望只返回那些经理至少具有三名雇员的部门，则可以编写以下查询：
SELECT * FROM Departments AS D WHERE (SELECT COUNT(*) FROM fn_getsubtree(D.deptmgrid)) >= 3 deptid deptname deptmgrid ----------- ------------------------- ----------- 1 HR 2 2 Marketing 7
对新的 DRI 操作的支持： SET DEFAULT 和 SET NULL
ANSI SQL 定义了四个可能的引用操作，以支持 FOREIGN KEY 约束。您可以指定这些操作，以表明您希望系统如何响应针对由外键引用的表的 DELETE 或 UPDATE 操作。SQL Server 2000 支持这些操作中的两个：NO ACTION 和 CASCADE。SQL Server 2005 Beta 2 添加了对 SET DEFAULT 和 SET NULL 引用操作的支持。
SET DEFAULT 和 SET NULL 引用操作扩展了声明性引用完整性 (DRI) 功能。您可以在外键声明中将这些选项与 ON UPDATE 和 ON DELETE 子句结合使用。SET DEFAULT 意味着，当您在被引用的表中删除行 (ON DELETE) 或更新被引用的键 (ON UPDATE) 时，SQL Server 会将引用表中的相关行的引用列值设置为该列的默认值。类似地，如果您使用 SET NULL 选项，则 SQL Server 可以通过将值设置为 NULL 进行反应（前提是引用列允许使用空值）。
例如，以下 Customers 表具有三个真实客户和一个虚拟客户：
CREATE TABLE Customers ( customerid CHAR(5) NOT NULL, /* other columns */ CONSTRAINT PK_Customers PRIMARY KEY(customerid) )
INSERT INTO Customers VALUES('DUMMY') INSERT INTO Customers VALUES('FRIDA') INSERT INTO Customers VALUES('GNDLF') INSERT INTO Customers VALUES('BILLY')
Orders 表跟踪定单。不一定非要将定单分配给真实客户。如果您输入一个定单并且未指定客户 ID，则默认情况下会将 DUMMY 客户 ID 分配给该定单。在从 Customers 表中进行删除时，您希望 SQL Server 在 Orders 中的相关行的 customerid 列中设置 NULL。customerid 列中含有 NULL 的定单成为“孤儿”，也就是说，它们不属于任何客户。假设您还希望允许对 Customers 中的 customerid 列进行更新。您可能希望将对 Orders 中的相关行进行的更新级联，但是假设公司的业务规则另行规定：应当将属于 ID 被更改的客户的定单与默认客户 (DUMMY) 相关联。在对 Customers 中的 customerid 列进行更新时，您希望 SQL Server 将默认值 'DUMMY' 设置为 Orders 中的相关客户 ID (customerid)。您用外键按如下方式创建 Orders 表，并且用一些定单填充它：
CREATE TABLE Orders ( orderid INT NOT NULL, customerid CHAR(5) NULL DEFAULT('DUMMY'), orderdate DATETIME NOT NULL, CONSTRAINT PK_Orders PRIMARY KEY(orderid), CONSTRAINT FK_Orders_Customers FOREIGN KEY(customerid) REFERENCES Customers(customerid) ON DELETE SET NULL ON UPDATE SET DEFAULT ) INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101') INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102') INSERT INTO Orders VALUES(10003, 'BILLY', '20040101') INSERT INTO Orders VALUES(10004, 'BILLY', '20040103') INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104') INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')
要测试 SET NULL 和 SET DEFAULT 选项，请发出下列 DELETE 和 UPDATE 语句：
DELETE FROM Customers WHERE customerid = 'FRIDA' UPDATE Customers SET customerid = 'DOLLY' WHERE customerid = 'BILLY'
结果，FRIDA 的定单被分配 customerid 列中的空值，而 BILLY 的定单被分配 DUMMY：
orderid customerid orderdate ----------- ---------- ---------------------- 10001 NULL 1/1/2004 12:00:00 AM 10002 NULL 1/2/2004 12:00:00 AM 10003 DUMMY 1/1/2004 12:00:00 AM 10004 DUMMY 1/3/2004 12:00:00 AM 10005 GNDLF 1/4/2004 12:00:00 AM 10006 GNDLF 1/5/2004 12:00:00 AM
DELETE FROM Customers WHERE customerid = 'DUMMY' UPDATE Customers SET customerid = 'GLDRL' WHERE customerid = 'GNDLF' .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Orders_Customers'. The conflict occurred in database 'tempdb', table 'Customers', column 'customerid'. The statement has been terminated.
设为主页
收藏本站
相关文章: