使用 APPLY 来为每行调用表值函数

 

USE AdventureWorks


-----使用 CROSS APPLY ------

-----创建表值函数-----
CREATE FUNCTION dbo.fn_WorkOrderRouting
(@WorkOrderID int) RETURNS TABLE
AS
RETURN
SELECT WorkOrderID,
ProductID,
OperationSequence,
LocationID
FROM Production.WorkOrderRouting
WHERE WorkOrderID = @WorkOrderID
GO

-----将Production.WorkOrder表中的WorkOrderID传入新的函数

SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder w
CROSS APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
ORDER BY w.WorkOrderID,
w.OrderQty,
r.ProductID


---使用 OUTER APPLY

INSERT INTO [AdventureWorks].[Production].[WorkOrder]
(ProductID,
OrderQty,
ScrappedQty,
StartDate,
EndDate,
DueDate,
ScrapReasonID,
ModifiedDate
)
VALUES
(1,
1,
1,
GETDATE(),
GETDATE(),
GETDATE(),
1,
GETDATE()
)


SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder AS w
CROSS APPLY dbo.fn_WorkOrderRouting
(w.WorkOrderID) AS r
WHERE w.WorkOrderID IN
(SELECT MAX(WorkOrderID)
FROM Production.WorkOrder)