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

希望能对您有所帮助。

我意识到您不能在SQL视图中拥有Temp表，因此什么是转换下面查询的最佳方法，以便可以在SQL视图中使用它。

提前致谢

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

最佳答案

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

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'