问题描述
我有下表
表一
Id WFID data1 data2
1 12 'd' 'e'
1 13 '3' '4f'
1 15 'e' 'dd'
2 12 'f' 'ee'
3 17 'd' 'f'
2 17 'd' 'f'
4 12 'd' 'f'
5 20 'd' 'f'
从此表中，我只想选择仅包含 12 和 17 的行.就像从表中我只想检索不同 id 的 2,3 和 4.1 被排除，因为它有 12 但也有 13 和 15.5 被排除，因为它有 20.
From this table I just want to select the rows which has 12 and 17 only exclusively. Like from the table I just want to retrieve the distinct id's 2,3 and 4. 1 is excluded because it has 12 but also has 13 and 15. 5 is excluded because it has 20.
- 包含 2 个，因为它只有 12 个和 17 个.
- 包含 3 个，因为它只有 17 个
- 包含 4 个，因为它只有 12 个
非常感谢任何帮助.
推荐答案
如果你只想要满足条件的不同 id 的列表，你可以使用带有 的聚合和过滤器具有子句:
If you just want the list of distinct ids that satisfy the conditions, you can use aggregation and filter with a having clause:
select id
from mytable
group by id
having max(case when wfid not in (12, 17) then 1 else 0 end) = 0
这会过滤掉具有除 12 或 17 之外的任何 wfid 的组.
This filters out groups that have any wfid other than 12 or 17.
如果你想要整个对应的行，那么窗口函数更合适:
If you want the entire corresponding rows, then window functions are more appropriate:
select
from (
select t.*,
max(case when wfid not in (12, 17) then 1 else 0 end) over(partition by id) flag
from mytable t
) t
where flag = 0
这篇关于从表中选择每个 id 具有特定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！