MSSQL嵌套查询在一列上不同

Question

我使用的是MSSQL Server 2008。我有三个表如下:

表:项目列:projecd，{主键}ProjectNickName, BusinessType

表:审计列:{外键}ProjectNickName，{主键}audittid, AuditCreationDate, AuditStatus

表:发现列:{外键}AuditID, FindingStatus，{主键}FindingNumber

项目审核:一对多

审计发现:一对多

我想找到AuditStatus为'Open'的所有审计记录，这些记录没有'Open' FindingStatus的发现。此外，审计记录必须与具有businessstype为'External'且AuditCreationDate在最近30天内的项目相关联。

---

到目前为止我写的是:

Select a.AuditID, p.BusinessType, p.ProjectNickName, a.AuditCreationDate 
FROM Project p 
INNER JOIN Audit a ON p.ProjectNickName = a.ProjectNickName 
LEFT OUTER JOIN Findings f on a.AuditID = f.AuditID 
WHERE p.BusinessType LIKE 'External' AND AuditCreationDate >= GETDATE()-30 
      AND a.AuditStatus LIKE 'OPEN' and f.FindingStatus NOT LIKE 'OPEN'

上面的查询有两个问题。

1. 每次审计可以有多个发现，但如果其中任何一个发现的状态为"打开"，我不希望显示该记录。
2. 以上没有显示不同的审核记录。如果有多个审计结果不具有"Open"状态，它将显示它们。我只想要不同的审计记录。我只针对一个列查看了下面的DISTINCT，它解决了这个问题，但是审计记录仍然会显示是否至少有一个发现没有"Open"findingStatus，以及与审计相关的另一个发现是否有"Open"findingStatus。如前所述，我只希望没有查找记录且查找状态为'Open'的审计出现在我的结果集中。

我猜我可能需要使用某种嵌套查询与一个不同的函数，但我不能围绕我的头脑如何做到这一点。

任何帮助都非常感谢!我已经尽量说得清楚和准确了，但如果有什么需要我详细说明的，请告诉我。

Answer 1

Select 
    a.AuditID, 
    p.BusinessType, 
    p.ProjectNickName, 
    a.AuditCreationDate 
FROM 
    Project p 
JOIN 
    Audit a 
    ON p.ProjectNickName = a.ProjectNickName AND
    a.AuditCreationDate >= GETDATE()-30 AND 
    a.AuditStatus ='OPEN'
LEFT JOIN 
    Findings f 
    on  a.AuditID = f.AuditID and
        f.FindingStatus ='OPEN'
WHERE 
    p.BusinessType ='External' AND      
    f.FindingStatus is null -- Here we could use any column name from the findings table. We are basically saying we don't want there to exist an associated findings entry that has the findingStatus open

Answer 2

您可以使用not exists子查询来排除具有打开结果的审计。这也将消除每次审计的多行，因为您不再连接finding表。

select  a.AuditID
,       p.BusinessType
,       p.ProjectNickName
,       a.AuditCreationDate 
from    Project p 
join    Audit a 
on      p.ProjectNickName = a.ProjectNickName 
where   p.BusinessType LIKE 'External' and 
        a.AuditStatus LIKE 'OPEN' and
        a.AuditCreationDate >= getdate()-30 and 
        not exists
        (
        select  *
        from    Findings f 
        where   a.AuditID = f.AuditID and
                f.FindingStatus = 'OPEN'
        )

Answer 3

您可能可以使用像这样的相关子查询。

SELECT * FROM Audit A WHERE AuditID NOT IN 
(SELECT AuditID FROM Findings 
WHERE AuditID = A.AuditID and Status = 'OPEN')

应该禁止任何具有Status = 'Open'的发现的审计

您需要与Project JOIN一起完成它，等等。

Answer 4

您没有指定是否有必要显示行，如果审计在发现表中没有行(即将返回null与左外连接)。假设你确实想要显示它们，这里有一种方法。

Select a.AuditID, p.BusinessType, p.ProjectNickName, a.AuditCreationDate 
FROM Project p 
INNER JOIN Audit a ON p.ProjectNickName = a.ProjectNickName 
WHERE p.BusinessType LIKE 'External' 
AND AuditCreationDate >= GETDATE()-30 
AND a.AuditStatus LIKE 'OPEN' 
AND a.AuditID NOT IN
(select distinct AuditID from Findings
  where FindingStatus LIKE 'OPEN')