sql server—如何在sql中正确查询复杂连接语句的结果？

pkbketx9 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(401)

新的高级sql！
我正在尝试编写一个查询，返回此查询的结果列的计数（*）和总和：

DECLARE @Id INT = 1000;

SELECT 
    *,
    CASE
        WHEN Id1 >= 6 THEN 1
        ELSE 0
    END AS Tier1,
    CASE
        WHEN Id1 >= 4 THEN 1
        ELSE 0
    END AS Tier2,
    CASE
        WHEN Id1 >= 2 THEN 1
        ELSE 0
    END AS Tier3
    FROM (
        SELECT
            Org.OrgID,
            App.AppID,
            App.FirstName,
            App.LastName,
            MAX(AppSubmitU_Level.Id1) AS Id1
        FROM Org
        INNER JOIN AppEmployment
            ON AppEmployment.OrgID = Org.OrgID
        INNER JOIN App
            ON App.AppID = AppEmployment.AppID
        INNER JOIN AppSubmit
            ON App.AppID = AppSubmit.AppID
        INNER JOIN AppSubmitU_Level
            ON AppSubmit.LevelID = AppSubmitU_Level.Id1
        INNER JOIN AppEmpU_VerifyStatus
            ON AppEmpU_VerifyStatus.VerifyStatusID = AppEmployment.VerifyStatusID
        WHERE AppSubmitU_Level.SubmitTypeID = 1 -- Career
        AND AppEmpU_VerifyStatus.StatusIsVerified = 1
        AND AppSubmit.[ExpireDate] IS NOT NULL
        AND AppSubmit.[ExpireDate] > GETDATE()
        AND Org.OrgID = @Id
        GROUP BY
            Org.OrgID,
            App.AppID,
            App.FirstName,
            App.LastName
    ) employees

我尝试将@id移到原始查询之外，并在顶部添加select（*）、sum和sum，如下所示：

DECLARE @OrgID INT = 1000;

SELECT COUNT(*), SUM(employees.Tier1), SUM(employees.Tier2), SUM(employees.Tier3) 
FROM
    (SELECT *,
        ...
    ) AS employees
);

但是，当我运行查询时，得到的错误是：
无法绑定多部分标识符employees.tier1
我的sum语句中的其他标识符也会出现同样的错误。
我假设这与以下事实有关：tier1、tier2和tier3列是由my from（）中的内部联接查询返回的，而不是由我正在查询的现有表设置的值。但我不知道如何重写它来正确初始化。
提前感谢您的帮助！

sql sql-server subquery tsql cout

来源：https://stackoverflow.com/questions/63250414/how-do-you-properly-query-the-result-of-a-complex-join-statement-in-sql

1条答案

按热度按时间

yc0p9oo01#

这是一个范围问题： employees 仅在子查询中定义，在外部范围中不可用。您基本上需要为外部查询设置别名：

DECLARE @OrgID INT = 1000;
SELECT COUNT(*), SUM(employees.Tier1) TotalTier1, SUM(employees.Tier2) TotalTier2, SUM(employees.Tier3) TotalTier3
FROM (
    SELECT *,
    ...
    ) AS employees
) AS employees;
--^ here

注意，我在外部查询中添加了列别名，这在sql中是一个很好的实践。
如果对外部查询使用另一个别名，可能更容易理解发生了什么：

SELECT COUNT(*), SUM(e.Tier1), SUM(e.Tier2), SUM(e.Tier3) 
FROM (
    SELECT *,
    ...
    ) AS employees
) AS e;

注意，实际上不需要在外部查询中限定列名，因为列名是明确的。
最后：实际上不需要子查询。您可以将查询编写为：

SELECT 
    SUM(CASE WHEN Id1 >= 6 THEN 1 ELSE 0 END) AS TotalTier1,
    SUM(CASE WHEN Id1 >= 4 THEN 1 ELSE 0 END) AS TotalTier2,
    SUM(CASE WHEN Id1 >= 2 THEN 1 ELSE 0 END) AS TotalTier3
FROM (
    SELECT
        Org.OrgID,
        App.AppID,
        App.FirstName,
        App.LastName,
        MAX(AppSubmitU_Level.Id1) AS Id1
    FROM Org
    INNER JOIN AppEmployment
        ON AppEmployment.OrgID = Org.OrgID
    INNER JOIN App
        ON App.AppID = AppEmployment.AppID
    INNER JOIN AppSubmit
        ON App.AppID = AppSubmit.AppID
    INNER JOIN AppSubmitU_Level
        ON AppSubmit.LevelID = AppSubmitU_Level.Id1
    INNER JOIN AppEmpU_VerifyStatus
        ON AppEmpU_VerifyStatus.VerifyStatusID = AppEmployment.VerifyStatusID
    WHERE AppSubmitU_Level.SubmitTypeID = 1 -- Career
    AND AppEmpU_VerifyStatus.StatusIsVerified = 1
    AND AppSubmit.[ExpireDate] IS NOT NULL
    AND AppSubmit.[ExpireDate] > GETDATE()
    AND Org.OrgID = @Id
    GROUP BY
        Org.OrgID,
        App.AppID,
        App.FirstName,
        App.LastName
) employees

赞(0）回复(0）举报 2021-07-24

我来回答

sql server—如何在sql中正确查询复杂连接语句的结果？

1条答案

相关问题

热门标签

最新问答