查找符合特定条件的记录组

问题描述

我有以下数据:

ID --- ParentID --- DataValue  
1  ---    1     ---    A  
2  ---    1     ---    B  
3  ---    1     ---    C  
4  ---    4     ---    B  
5  ---    4     ---    C  
6  ---    6     ---    A  
7  ---    6     ---    B  
8  ---    6     ---    C  
9  ---    6     ---    D

对于每组记录(按ParentID分组)，我想找到所有没有包含A"作为DataValue的记录的组

For each group of records (grouped by ParentID), I would like to find all groups that do not have a record containing "A" as a DataValue

由于第 1 组和第 6 组确实包含至少一个将A"作为 DataValue 的记录，因此我不想看到它们.我只想查看记录 4 和 5(属于第 4 组的一部分)，因为该组中没有带有A"的记录.

Since groups 1 and 6 do contain at least one record that has "A" as a DataValue, I would not want to see them. I would only like to see records 4 and 5 (which are a part of group 4) since there are no records in this group that have an "A".

非常感谢任何帮助！

推荐答案

SELECT
  ID,
  ParentID,
  DataValue
FROM
  MyTable
WHERE
  NOT EXISTS (
    SELECT 1 
      FROM MyTable i
     WHERE i.ParentId = MyTable.ParentId AND i.DataValue = 'A'
  )

如果表很大，建议使用 (ParentId, DataValue) 上的索引.

An index over (ParentId, DataValue) is recommendable if the table is large.