MSSQL嵌套查询在一列上不同

I am using MSSQL Server 2008. I have three tables as follows:

我正在使用MSSQL Server 2008.我有三个表如下:

Table:Project Columns: ProjectID, {Primary Key} ProjectNickName, BusinessType

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

Table:Audit Columns: {Foreign Key}ProjectNickName, {Primary Key}AuditID, AuditCreationDate, AuditStatus

表:审核列:{外键} ProjectNickName,{主键} AuditID,AuditCreationDate,AuditStatus

Table:Findings Columns: {Foreign Key}AuditID, FindingStatus, {Primary Key}FindingNumber

表:结果列:{外键} AuditID,FindingStatus,{主键} FindingNumber

Project to Audit: One to many

审计项目:一对多

Audit to Findings: One to many

审核结果:一对多

I want to find all audit records with an AuditStatus of 'Open' that have NO findings with an 'Open' FindingStatus. Also the audit record must be associated with a project that has a BusinessType of 'External' and the AuditCreationDate is within the last 30 days.

我想找到审计记录为“Open”的所有审计记录,这些记录在“Open”FindingStatus中没有任何结果。此外,审计记录必须与BusinessType为“External”且AuditCreationDate在过去30天内的项目相关联。

Here is what I have so far:

这是我到目前为止:

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'

There are two problems with the above query.

上述查询存在两个问题。

There can be multiple findings for each audit, but if any one of those findings has a status of 'Open', I do not want that record to show.

每次审核都可以有多个结果,但如果其中任何一个结果的状态为“开放”,我不希望显示该记录。

The above does not show disctinct audit records. If there are multiple findings for an audit that do not have an 'Open' status, it will show them. I only want distinct audit records. I looked at the following DISTINCT for only one Column, which solved that problem, but an audit record would still show if there was at least one finding that did not have an 'Open' findingStatus and another finding associated with the audit that DID have an 'Open' findingStatus. As mentioned previously, I only want audits that have no finding records with a findingStatus of 'Open' to appear in my result set.

以上内容并未显示分析审计记录。如果审核中有多个没有“打开”状态的发现,则会显示它们。我只想要不同的审计记录。我查看了以下DISTINCT只有一个列,它解决了这个问题,但审计记录仍然会显示是否至少有一个发现没有'开放'的findsStatus和另一个与DID有关的审计相关的发现'开放'发现状态。如前所述,我只希望审核没有找到记录,并且查找状态为“打开”,以显示在我的结果集中。

I am guessing I may need to use some kind of nested query with a distinct function, but I can't wrap my mind around how to do it yet.

我猜我可能需要使用某种具有独特功能的嵌套查询,但我无法将其包含在如何做到这一点。

Any help is much appreciated! I tried to be as clear and precise as possible, but let me know if I can elaborate on anything.

任何帮助深表感谢!我试图尽可能清晰和准确,但如果我可以详细说明任何事情,请告诉我。

4 个解决方案

#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

#2

You could use a not exists subquery to exclude audits with open findings. That will also eliminate multiple rows per audit, since you are no longer joining on the findings table.

您可以使用不存在的子查询来排除具有未知结果的审核。这也将消除每次审核的多行,因为您不再加入调查结果表。

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'
        )

#3

You might be able to use a correlated subquery such as this one.

您可以使用相关的子查询,例如此子查询。

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

Which ought to disallow any Audits that have Findings with Status = 'Open'

哪个应该禁止任何具有状态='打开'的结果的审计

You'll need to complete it with your Project JOIN, et cetera.

您需要使用Project JOIN等完成它。

#4

You didn't specify whether it's necessary to display the row if the audit has no row in findings table at all (i.e. will return null with a left outer join). Assuming that you do want them to show, here is one way of doing it.

如果审计在发现表中根本没有行,则没有指定是否有必要显示该行(即,将使用左外连接返回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')

#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

#2