时间戳

首页 > 解决方案 > 如何正确查询 SQL 中复杂连接语句的结果？

sql - 如何正确查询 SQL 中复杂连接语句的结果？

问题描述

高级 SQL 新手！

我正在尝试编写一个查询，该查询返回来自该查询的结果列的 COUNT(*) 和 SUM：

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 语句中的其他标识符也会出现相同的错误。

我假设这与我的 FROM() 中的内部连接查询返回 Tier1、Tier2 和 Tier3 列的事实有关，而不是我正在查询的现有表设置的值。但我不知道如何重写它以正确初始化。

在此先感谢您的帮助！

标签： sqlsql-servertsqlsubquerycout

解决方案

这是一个范围问题：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

推荐阅读