问题描述
我有以下数据:
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.