目录
1.使用CROSS APPLY
CREATE FUNCTION dbo.fn_WorkOrderRouting
use adventureworks
go
CREATE FUNCTION dbo.fn_WorkOrderRouting
(@WorkOrderID int) RETURNS TABLE
AS
RETURN
SELECT WorkOrderID,
ProductID,
OperationSequence,
LocationID
FROM Production.WorkOrderRouting
WHERE WorkOrderID=@WorkOrderID
GO
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
2.使用OUTER APPLY
use adventureworks
go
INSERT INTO [AdventureWorks].[Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate]
)
VALUES
(1
,1
,1
,GETDATE()
,GETDATE()
,GETDATE()
,1
,GETDATE()
)
go
SELECT w.WorkOrderID,
w.OrderQty,
r.ProductID,
r.OperationSequence
FROM Production.WorkOrder as w
OUTER APPLY dbo.fn_WorkOrderRouting(w.WorkOrderID) as r
WHERE w.WorkOrderID IN
(SELECT MAX(WorkOrderID)
FROM Production.WorkOrder)