使用PATINDEX时，请选择DISTINCT字段

Question

我有一个名为Issues的表，需要从中获取数据。我加入了Issues表和另一个名为IssueActivities的表。因此，对于IssueID的每个实例，可能有1到多个IssueActivity。IssueActivities表中有一个名为Notes的字段，其数据类型为text。我正在尝试选择IssueID的DISTINCT列表，其中Notes字段不包含2个特定字符串。

这是我的SQL:

SELECT DISTINCT i.IssueID
FROM Issues i
INNER JOIN IssueActivities ia ON i.IssueID = ia.IssueID
WHERE i.IssueStatusID = 2 --Closed issues only
AND (PATINDEX('%Pending DR%', ia.Notes) < 1 AND PATINDEX('%Pending E%', ia.Notes) < 1)

此sql的问题在于，它为具有该条件的问题返回IssueID，因为一个Issue可以具有多个IssueActivity，因此并非所有行都包含该条件。这有道理吗？这里有一个快速的例子：

问题表

IssueID | IssueStatusID
-----------------------
1700       2
1701       2

问题活动表

IssueActivityID | IssueID | Notes
---------------------------------
1                 1700      Issue Entered
2                 1700      Sub Status changed from New to In Progress
3                 1700      Sub Status changed from In Progress to Pending DR
4                 1701      Issue Entered
5                 1701      Issue Assigned
6                 1701      Sub Status changed from New to Closed

因此，从上表中，我只想得到issue 1701，因为所有属于它的IssueActivity都不包含我正在使用的标准。

非常感谢您的帮助。

Answer 1

您应该找到要排除的行，然后在where子句中消除它们。本例使用APPLY为每个Issues行查找其中包含"Pending DR"或"Pending E"的IssueActivity行。这是一个SQLFiddle

SELECT i.IssueID
FROM Issues i
OUTER APPLY(
  SELECT TOP 1 ia.IssueActivityID
  FROM IssueActivities ia
  WHERE ia.IssueID = i.IssueID
    AND (PATINDEX('%Pending DR%', ia.Notes) > 0
      OR PATINDEX('%Pending E%', ia.Notes) > 0)
) pending
WHERE i.IssueStatusID = 2
  AND pending.IssueActivityID IS NULL

Answer 2

如果我理解正确，您需要首先选择要忽略的内容：

SELECT DISTINCT IssueID
FROM IssuesActivities
WHERE PATINDEX('%Pending DR%', ia.Notes) > 0 OR PATINDEX('%Pending E%', ia.Notes) > 0

然后你可以忽略它：

SELECT DISTINCT i.IssueID
FROM Issues i
LEFT JOIN (SELECT DISTINCT IssueID
           FROM IssuesActivities
           WHERE PATINDEX('%Pending DR%', ia.Notes) > 0 OR PATINDEX('%Pending E%', ia.Notes) > 0
          )ia
  ON i.IssueID = ia.IssueID
WHERE i.IssueStatusID = 2 --Closed issues only
   AND ia.IssueID IS NULL

Answer 3

您需要将对IssueActivities的引用替换为一个子查询，该子查询仅返回您不想包含的表中的记录，将联接更改为外部联接以便返回Issues中的所有记录，并将条件更改为仅在IssueActivity查询没有匹配记录的情况下返回Issues记录。

SELECT DISTINCT i.IssueID
FROM Issues AS i LEFT OUTER JOIN
      (SELECT * FROM IssueActivities WHERE (PATINDEX('%Pending DR%', ia.Notes) < 1)
       AND (PATINDEX('%Pending E%', ia.Notes) < 1)) AS ia ON i.IssueID = ia.IssueID
WHERE (i.IssueStatusID = 2) AND ia.IssueID IS NULL

你也可以使用：

SELECT i.IssueID
FROM Issues AS i
WHERE (i.IssueStatusID = 2) AND NOT EXISTS (SELECT * FROM IssueActivities AS ia 
WHERE (PATINDEX('%Pending DR%', ia.Notes) < 1) AND (PATINDEX('%Pending E%', ia.Notes) < 1)
      AND (i.IssueID = ia.IssueID)) )