Power BI直连查询中递归CTE转派生表的实现求助

Power BI直连查询会自动在输入SQL前拼接Select * From(，导致存储过程、变量声明、CTE表达式无法正常执行。针对提取出的递归CTE代码，可通过以下方式转换为兼容派生表的形式，完整保留递归逻辑：

原始递归CTE代码 #

WITH MaterialsTree(PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
      StepNo,PartWt,Materials_ID,ItemNo, ParentID) AS 
(
    SELECT PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
      StepNo,PartWt,Materials_ID,ItemNo, CAST(NULL AS INT) AS ParentID
        FROM Materials AS Mat
        WHERE PartNo = @PartNo
    UNION ALL
    SELECT m.PartNo,m.SubPartNo,m.Descrip,m.Qty,m.Unit,m.Purchased,m.Vendor,m.TotalQty,m.UnitCost,m.UnitPrice,m.TotalCost,m.TotalPrice,m.TotalWt,
      m.StepNo,m.PartWt,m.Materials_ID,m.ItemNo, t.Materials_ID AS ParentID
        FROM Materials AS m
        INNER JOIN MaterialsTree AS t 
            ON m.PartNo = t.SubPartNo
)
SELECT PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
      StepNo,PartWt,Materials_ID,ItemNo, ParentID 
FROM MaterialsTree

转换后兼容Power BI的代码 #

SELECT *
FROM (
    WITH MaterialsTree(PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
          StepNo,PartWt,Materials_ID,ItemNo, ParentID) AS 
    (
        SELECT PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
          StepNo,PartWt,Materials_ID,ItemNo, CAST(NULL AS INT) AS ParentID
            FROM Materials AS Mat
            WHERE PartNo = ? -- 用?作为参数占位符，在Power BI中配置对应查询参数传递值
        UNION ALL
        SELECT m.PartNo,m.SubPartNo,m.Descrip,m.Qty,m.Unit,m.Purchased,m.Vendor,m.TotalQty,m.UnitCost,m.UnitPrice,m.TotalCost,m.TotalPrice,m.TotalWt,
          m.StepNo,m.PartWt,m.Materials_ID,m.ItemNo, t.Materials_ID AS ParentID
            FROM Materials AS m
            INNER JOIN MaterialsTree AS t 
                ON m.PartNo = t.SubPartNo
    )
    SELECT PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
          StepNo,PartWt,Materials_ID,ItemNo, ParentID 
    FROM MaterialsTree
) AS DerivedMaterialsTree

关键说明 #

• 将完整递归CTE逻辑嵌套到子查询中，确保Power BI自动添加的Select * From(不会破坏CTE语法结构
• 把原存储过程中的变量@PartNo替换为参数占位符?，在Power BI中创建对应查询参数并绑定，实现动态传递值的需求

内容的提问来源于stack exchange，提问作者LastSparton