sql-server - 无法使用临时表创建 View

Question

希望能帮到你。

我意识到 SQL View 中不能有临时表，因此转换下面的查询以便可以在 SQL View 中使用的最佳方法是什么。

提前致谢

SELECT 
    a.KeyField, a.AlphaValue AS Compostable 
INTO 
    #DAT
FROM 
    [SysproCompanyA].[dbo].AdmFormData a
WHERE 
    a.FieldName = 'DAT001'

SELECT 
    b.KeyField, b.AlphaValue AS Trial 
INTO 
    #PAS
FROM 
    [SysproCompanyA].[dbo].AdmFormData b
WHERE 
    b.FieldName = 'PAS001'

SELECT 
    c.KeyField AS JobNumber, c.Compostable, d.Trial
FROM 
    #DAT c 
INNER JOIN 
    #PAS d ON c.KeyField = d.KeyField
WHERE 
    c.KeyField = '00170579'

DROP TABLE #DAT
DROP TABLE #PAS

Answer 1

也许我们可以将两个表连接在一起，具有相同的限制，然后选择所需的列:

SELECT
    c.KeyField AS JobNumber,
    c.Compostable,
    d.Trial
FROM [SysproCompanyA].[dbo].AdmFormData c
INNER JOIN [SysproCompanyA].[dbo].AdmFormData d
    ON c.KeyField = d.KeyField
WHERE
    c.FieldName = 'DAT001' AND
    d.FieldName = 'PAS001' AND
    c.KeyField = '00170579'