从表中选择每个 id 具有特定值的行 [英] Selecting rows from a table with specific values per id

问题描述

我有下表

表一

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 具有特定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！