查找符合特定条件的记录组
我有以下数据:
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.
相关文章