查询排除两列中的值

Question

我在论坛里找不到这个，更不知道如何描述它。

设置：我有一个数据库，在那里我加入了很多表，其中包含人员、他们的作业和时间条目。这是一个展开视图，因此时间条目具有与其关联的属性，项目也具有自己的属性。时间条目和项目都有一个与他们相关的客户

问题是：我需要执行几个差异查询。该人员不应向未分配的项目收费。我只想看看那些。我遇到的挑战是，是否可以将该人员分配给多个客户。表的连接方式在与时间条目数据连接后可能会产生误导。IE:

Person | Assigned Customer | Time Entry Customer
Joe    | Customer A        | Customer C
Joe    | Customer B        | Customer C
Joe    | Customer B        | Customer A

在上面的示例中，行1和3的输出应该被过滤掉，因为"Joe"被分配给客户A（行1），并向客户A收费（行3）。

我试着做一个EXISTS语句来过滤掉东西，但我就是无法理解语法（我知道结果和下面的查询中没有EXISTS子句一样）：

SELECT A.[Assigned Engineer]
    ,YEAR(A.[Date]) AS [Year]
    ,dbo.ISOweek(A.[Date]) AS [Week]
    ,A.[Customer] AS [Charging Customer]
    ,A.[Project Customer] AS [Project Customer]
    ,SUM(A.[Hours]) AS [Hours]
FROM SkyNet.dbo.PROJECT_ASSIGNMENT_AND_CHARGES_DASHBOARD A
WHERE A.[Customer] NOT LIKE '%' + A.[Project Customer] +'%'
    AND YEAR(A.[Date])=2015
    AND dbo.ISOweek(A.[Date])=1
    AND EXISTS
    (SELECT B.[Assigned Engineer]
    ,YEAR(B.[Date]) AS [Year]
    ,dbo.ISOweek(A.[Date]) AS [Week]
    ,B.[Customer] AS [Charging Customer]
    ,B.[Project Customer] AS [Project Customer]
    ,SUM(B.[Hours]) AS [Hours]
    FROM SkyNet.dbo.PROJECT_ASSIGNMENT_AND_CHARGES_DASHBOARD B
    WHERE A.[Assigned Engineer]=B.[Assigned Engineer]
        AND A.[Customer] NOT LIKE '%' + B.[Project Customer] +'%'
    GROUP BY B.[Assigned Engineer], YEAR(B.[Date]), dbo.ISOweek(B.[Date]),     B.[Customer], B.[Project Customer]
    )
GROUP BY A.[Assigned Engineer], YEAR(A.[Date]), dbo.ISOweek(A.[Date]), A.    [Customer], A.[Project Customer]
ORDER BY A.[Assigned Engineer]

任何帮助都将不胜感激！

---

编辑-2/27/2015

以下是一些要求的样本数据：

 Assigned Engineer | Date     | Charging Customer | Project Customer | Hours
 Joe               | 1/4/2015 | Customer A - EAST | Customer A       | 8
 Joe               | 1/4/2015 | Customer B        | Customer A       | 16
 Joe               | 1/4/2015 | Customer A - EAST | Customer C       | 8
 Joe               | 1/4/2015 | Customer B        | Customer C       | 16

Answer 1

这可以通过执行左联接来解决吗？在我的联接中，我将查看与指定客户相匹配的行，以及与项目客户具有相同客户的行。然后，在where子句中，我只在找不到匹配项的地方向后拉几行。

SELECT A.[Assigned Engineer]
    ,YEAR(A.[Date]) AS [Year]
    ,dbo.ISOweek(A.[Date]) AS [Week]
    ,A.[Customer] AS [Charging Customer]
    ,A.[Project Customer] AS [Project Customer]
    ,SUM(A.[Hours]) AS [Hours]
FROM SkyNet.dbo.PROJECT_ASSIGNMENT_AND_CHARGES_DASHBOARD A
left outer join SkyNet.dbo.PROJECT_ASSIGNMENT_AND_CHARGES_DASHBOARD B
    ON A.[Assigned Engineer] = B.[Assigned Engineer] AND
       (B.[Customer] LIKE A.[Project Customer] + '%' = OR A.[Customer] LIKE B.[Project Customer] + '%')
WHERE b.[Customer] IS NULL

编辑：更改联接以反映OP的数据。

Answer 2

我相信我已经回答了自己的问题。。。我的前额只需要够到墙上的干墙：）我知道这会很简单，但我无法足够快地到达那里。使用COUNT和子查询，我能够过滤出收费客户与指定客户相似的所有记录。我希望这能在未来帮助其他人：

SELECT A.[Assigned Engineer]
    ,YEAR(A.[Date]) AS [Year]
    ,dbo.ISOweek(A.[Date]) AS [Week]
    ,A.[Customer] AS [Charging Customer]
    ,A.[Project Customer] AS [Project Customer]
    ,A.[Hours]
FROM SkyNet.dbo.PROJECT_ASSIGNMENT_AND_CHARGES_DASHBOARD A
WHERE A.[Customer] NOT LIKE '%' + A.[Project Customer] + '%'
    AND 1>(SELECT COUNT(*) 
        FROM SkyNet.dbo.PROJECT_ASSIGNMENT_AND_CHARGES_DASHBOARD B
        WHERE A.[Assigned Engineer]=B.[Assigned Engineer]
            AND YEAR(A.[Date])=YEAR(B.[Date])
            AND dbo.ISOweek(A.[Date])=dbo.ISOweek(B.[Date])
            AND A.[Customer] LIKE '%' + B.[Project Customer] + '%'
            AND A.[Hours]=B.[Hours])
ORDER BY A.[Assigned Engineer]
    ,YEAR(A.[Date])
    ,dbo.ISOweek(A.[Date])