如何将含临时表的SQL代码转换为可关联且自动更新的View？

直接把原代码里的临时表替换成CTE（公共表表达式），就能创建符合要求的视图。视图每次被查询时都会重新执行底层逻辑，保证数据和源表同步，同时支持独立运行或被其他代码关联调用。

完整的视图创建代码如下：

CREATE VIEW dbo.CaseInclusionStatus
AS
WITH Cases AS (
    SELECT c.[CaseID]
          ,b.dispositionCode
    FROM [XXXX].[dbo].[XXXX] c
    LEFT JOIN [XXXX].[dbo].[XXXX] b ON c.FinalDx = b.dispositionCode
    LEFT JOIN [XXXX].[dbo].[XXXX] LTLA WITH (NOLOCK) ON C.mappedLSOA = LTLA.LSOA11CD
    WHERE SiteTypeID = 5 
      AND LTLA.IsLatest = 1
      AND CallDate BETWEEN (SELECT MIN(calldate) FROM [XXXX].[dbo].[XXXX]) 
                      AND (SELECT MAX(calldate) FROM [XXXX].[dbo].[XXXX])
),
CaseMetrics AS (
    SELECT g.CaseId
          ,COUNT(DISTINCT quid) AS Questions
          ,DATEDIFF(SECOND, MIN(timein), MAX(timeout)) AS InOut
    FROM (
        SELECT caseID, quid, timein, timeout
        FROM [XXXX].[dbo].[XXXX] WITH (NOLOCK) 
        WHERE TimeIn >= DATEADD(day, -5, (SELECT MIN(calldate) FROM [XXXX].[dbo].[XXXX]))
          AND ActionId > 1 
          AND quId LIKE 'Tx%'
          AND AnswerNo IS NOT NULL 
          AND caseID IN (SELECT CaseID FROM Cases)
    ) g
    GROUP BY g.CaseId
)
SELECT a.CaseId,
       CASE 
           WHEN (Questions < 3 AND InOut < 5 AND dispositionCode IS NULL) THEN 'N' 
           WHEN (Questions IS NULL AND InOut < 5 AND dispositionCode IS NULL) THEN 'N' 
           ELSE 'Y' 
       END AS [Include]
FROM Cases a
LEFT JOIN CaseMetrics e ON a.caseId = e.CaseId

关键说明： #

• 用Cases和CaseMetrics两个CTE替代原代码中的临时表，完全规避临时表的使用限制
• 视图dbo.CaseInclusionStatus可以直接独立运行（执行SELECT * FROM dbo.CaseInclusionStatus），也能在其他代码中通过JOIN关联使用
• 每次查询视图时，都会重新计算所有逻辑，确保输出数据和源表实时同步
• 保留了原代码中的所有过滤条件、关联逻辑和计算规则，输出结果和原代码完全一致

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