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)
转载于:https://www.cnblogs.com/siyunianhua/archive/2011/12/26/2302683.html
相关资源：JAVA上百实例源码以及开源项目
