如何将指定CTE转换为子查询？适配PowerBI直接查询需求

转换后的子查询版本SQL #

SELECT  [Job #]
       ,[GlCode]
       ,[Date]
       ,[Variance Amt]
       ,[Job QTY]
       ,[OpenQty]
       ,[Part #]
       ,[Material]
       ,[PCS #]
       ,[Matrl$$]
       ,[Date Last Issue]
       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders]
       ,case when rn <> maxrn then 0 else [PO$$]           end as [PO$$]
       ,[Date Last Rcvd]
       ,case when rn <> maxrn then 0 when rn = maxrn then ([PO$$] + [Job Matrl$$]) else 0      end as [Wip Total]
       ,case when rn <> maxrn then 0 else [per pc]         end as [per pc]
       ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost]
       ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF]
       ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit]
FROM (
       SELECT [Job #]
       ,[GlCode]
       ,[Date]
       ,[Variance Amt]
       ,[Job QTY]
       ,[OpenQty]
       ,[Part #]
       ,[Material]
       ,[PCS #]
       ,[Matrl$$]
       ,[Date Last Issue]
       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders]
       ,[PO$$]
       ,[Date Last Rcvd]
       ,[Wip Total]
       ,[per pc]
       ,[Standard Cost]
       ,[DIFF]
       ,[% of Profit]
       ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Job #]) AS rn
       ,count(*) over(partition by [Job #]) as maxrn
       ,sum([Matrl$$]) over(partition by [Job #]) as [Job Matrl$$]
  FROM [CompanyZ].[dbo].[WIPVarianceRptView]
) AS cte
Order By [Job #]

转换方法说明 #

• 把原CTE定义里的查询逻辑，直接移到主查询的FROM子句中作为派生表（子查询），给它起别名cte，和原CTE名称保持一致，主查询的字段引用不需要做任何修改。
• 原代码中的rn <>是HTML转义字符，实际SQL中替换为rn <> maxrn即可正常执行。

关于子查询是否为更优编码实践 #

子查询并不是更优的编码方案：

• CTE（公共表表达式）的核心优势是可读性更强，能将复杂查询拆分为多个逻辑模块，分层清晰，后续维护、修改更便捷。
• 本次使用子查询只是适配PowerBI不支持CTE的限制，属于工具适配的妥协方案。从编码规范、可维护性角度，只要工具支持，优先选择CTE。

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