使用Cross Apply和Outer Apply关联表值函数中的字段

SQL Server中可以使用Cross Apply和Outer Apply关联表值函数中的字段，

示例代码如下：

 

CREATE DATABASE testdb20100729

GO

 

USE testdb20100729;

 

CREATE TABLE ParentAndChildren

(

     Id     INT ,

     Name   NVARCHAR(50),

     Parent INT,

     CONSTRAINT pk_ParentAndChildren PRIMARY KEY CLUSTERED

        (

            [Id] ASC

        ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

 

INSERT INTO ParentAndChildren(Id,Name,Parent)

SELECT 0,'root',null

UNION ALL

SELECT 1,'folrder1',0

UNION ALL

SELECT 2,'folrder2',0

UNION ALL

SELECT 3,'folrder3',0

UNION ALL

SELECT 4,'file1',1

UNION ALL

SELECT 5,'file2',2

UNION ALL

SELECT 6,'folrder4',3

UNION ALL

SELECT 7,'file3',6

 

GO

 

CREATE FUNCTION dbo.fn_GetChildren

(@Parent INT) RETURNS TABLE

AS

RETURN

SELECT

    Id,

    Name,

    Parent

FROM ParentAndChildren

WHERE Parent = @Parent

 

GO

 

SELECT p.Id, p.Name,c.Name as ChildName

FROM ParentAndChildren p

    CROSS APPLY dbo.fn_GetChildren(Id) AS c

ORDER BY p.Id

 

SELECT p.Id, p.Name,c.Name as ChildName

FROM ParentAndChildren p

    OUTER APPLY dbo.fn_GetChildren(Id) AS c

ORDER BY p.Id

 

GO

USE master;

DROP DATABASE testdb20100729;