时间戳

首页 > 解决方案 > 左连接的 SQL 计数

sql-server - 左连接的 SQL 计数

问题描述

在我的 Microsoft SQL Server 2016 数据库中，我试图确定有多少实验室（Lab_Space表）在去年完成了评估（EHS_Assessment_Audit表），按位置（Locations表）分组。每个实验室每年进行一次以上评估是很常见的。

到目前为止，我所做的一切都比实验室结果“完成”更多。例如：

WITH cte AS 
(
    SELECT DISTINCT 
        Lab_Space_Id
    FROM 
        EHS_Assessment_Audit
    WHERE 
        Audit_Date >= DATEADD(year, -1, GETDATE())
)
SELECT 
    l.Site_Name, l.Campus_Name, 
    COUNT(DISTINCT s.id) Total,
    SUM(CASE WHEN a.Lab_Space_ID IS NOT NULL THEN 1 ELSE 0 END) Audited
FROM
    Lab_Space s
LEFT OUTER JOIN 
    cte a ON s.id = a.Lab_Space_Id
JOIN 
    Locations l ON l.Building_Code = s.Building_Code
GROUP BY 
    l.Site_Name, l.Campus_Name
ORDER BY 
    l.Site_Name, l.Campus_Name

那里的 cte 应该为我提供一份已完成评估的实验室的唯一列表，然后我尝试按位置分组计数。我最终得到了输出，但它会说一个站点/校园组合总共有 178 个，经过审计的 1080 个。

标签： sql-server

解决方案

我认为在这种情况下使用 CTE 将比它的价值更麻烦。子查询将更容易阅读和修改。例如：

SELECT 
    l.Site_Name, 
    l.Campus_Name, 
    COALESCE(b.NumAudits, 0) as NumTotalAudits, 
    COALESCE(b.NumLabs, 0) as AuditedLabs
FROM Locations l
LEFT JOIN (
    SELECT s.Building_Code, COUNT(*) as NumAudits, COUNT(DISTINCT s.Lab_Space_Id) as NumLabs
    FROM Lab_Space s
    INNER JOIN EHS_Assessment_Audit a ON s.Lab_Space_Id = a.Lab_Space_Id
    WHERE a.Audit_Date >= DATEADD(year, -1, GETDATE())
    GROUP BY s.Building_Code
) as b ON l.Building_Code = b.Building_Code

使用过于简单的临时表和示例数据：

CREATE TABLE #EHS_Assessment_Audit (Lab_Space_Id int, Audit_Date datetime)
CREATE TABLE #Lab_Space (Lab_Space_Id int, Building_Code int)
CREATE TABLE #Locations (Location_Id int, Building_Code int, Site_Name nvarchar(30), Campus_Name nvarchar(30))

INSERT INTO #Locations VALUES (1, 1, 'Location1', 'Campus1'), (2, 2, 'Location2', 'Campus2')
INSERT INTO #Lab_Space VALUES (1, 1), (2, 1), (3, 2), (4, 2)
INSERT INTO #EHS_Assessment_Audit VALUES (1, '2018-10-11'), (1, '2018-09-11'), (2, '2018-10-11'), (3, '2015-10-11')

SELECT * FROM #Locations
SELECT * FROM #Lab_Space
SELECT * FROM #EHS_Assessment_Audit

SELECT 
    l.Site_Name, 
    l.Campus_Name, 
    COALESCE(b.NumAudits, 0) as NumTotalAudits, 
    COALESCE(b.NumLabs, 0) as AuditedLabs
FROM #Locations l
LEFT JOIN (
    SELECT s.Building_Code, COUNT(*) as NumAudits, COUNT(DISTINCT s.Lab_Space_Id) as NumLabs
    FROM #Lab_Space s
    INNER JOIN #EHS_Assessment_Audit a ON s.Lab_Space_Id = a.Lab_Space_Id
    WHERE a.Audit_Date >= DATEADD(year, -1, GETDATE())
    GROUP BY s.Building_Code
) as b ON l.Building_Code = b.Building_Code

推荐阅读