答案

1. 选择“数据”>“新建数据源”，并选择所需的数据源。
2. 在“服务器连接”对话框中，单击“初始 SQL”。
3. 在“初始 SQL”字段中输入您的 CTE。
   下面的示例使用“员工”表上的递归自联接用名为 OrganizationChart 的通用表表达式构建员工报告层次结构。语法根据数据源略有不同。 
   
   对于 SQL Server，我们将通过在查询的结尾使用“SELECT INTO [临时表名称]”（而不是 "SELECT"）来创建一个本地临时表： 
   WITH OrganizationChart (Id, [Name],  [Level], ManagerID) AS (
SELECT
Id, [Name], 0 as [Level], ManagerID
FROM
[dbo].[Employees] emp
WHERE
ManagerID IS NULL
UNION ALL
SELECT
emp.ID, emp.[Name], [Level] + 1, emp.ManagerID
FROM [dbo].[Employees] emp
INNER JOIN OrganizationChart d ON
emp.ManagerID = d.Id
)
SELECT  [Id], [Name], [Level], [ManagerID] INTO #ctedemo from OrganizationChart
   
   对于 Teradata，我们使用 CREATE VOLATILE TABLE [Temp Table Name] 创建了一个临时表： 
   CREATE VOLATILE TABLE ctedemo AS (WITH RECURSIVE OrganizationChart (Id, Name,  Level, ManagerID) AS (
SELECT
Id, Name, 0 as Level, ManagerID
FROM
TEST.Employees emp
WHERE
ManagerID IS NULL
UNION ALL
SELECT
emp.ID, emp.Name, Level + 1, emp.ManagerID
FROM TEST.Employees emp
INNER JOIN OrganizationChart d ON
emp.ManagerID = d.Id
) SELECT  Id, Name, Level, ManagerID from OrganizationChart) WITH DATA NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS
   
   对于 IBM DB2：
   DECLARE GLOBAL TEMPORARY TABLE SESSION.ctedemo(ID int, Name varchar(50), Level int, ManagerID int) on commit preserve rows with replace not logged;
INSERT INTO SESSION.ctedemo(ID, Name, Level, ManagerID) WITH OrganizationChart (Id, Name,  Level, ManagerID) AS (
SELECT
Id, Name, 0 as Level, ManagerID
FROM
Employees emp
WHERE
ManagerID IS NULL
UNION ALL
SELECT
emp.ID, emp.Name, Level + 1, emp.ManagerID
FROM Employees emp, OrganizationChart d
where
emp.ManagerID = d.Id
)
SELECT  Id, Name, Level, ManagerID from OrganizationChart
    
4. 创建一个自定义 SQL 查询以从您将记录插入到的临时表中进行选择。 
   例如，在 SQL Server 中： 
   SELECT * from #ctedemo
   在 Teradata 中： 
   SELECT * from ctedemo
   在 IBM DB2 中： 
   SELECT * FROM SESSION.ctedemo
    

其他信息