行过滤组

Question

下面是我得到的数据集。

人员、格式和不同角色(位)。

我想得到所有的行，其中每个格式组都选择了所有3个角色。对于例:新格式没有选择编辑器角色。所以我想得到不包含新记录的数据。

我怎样才能做到这一点？

​

​

Answer 1

您的第一个问题是“我想得到每个格式组都选择了所有3个角色的所有行”。您可以使用窗口函数来处理这个问题：

select name, format, write, director, editor
from (select t.*,
             max(cast(writer as int)) over (partition by format) as maxwriter,
             max(cast(director as int)) over (partition by format) as maxdirector,
             max(cast(editor as int)) over (partition by format) as maxeditor
      from table t
     ) t
where maxwriter = 1 and maxdirector = 1 and maxeditor = 1;

如果您想获得没有编辑器的行，可以使用类似的方法，只需更改where子句：

where maxwriter = 1 and maxdirector = 1 and maxeditor = 0;

Answer 2

select format
from your_table
group by format
having sum(case when writer = 1 then 1 else 0 end) > 0
and sum(case when director = 1 then 1 else 0 end) > 0
and sum(case when editor = 1 then 1 else 0 end) > 0

如果您需要完整的行而不是只需要format，那么您可以这样做

select * from your_table
where format in 
(
    select format
    from your_table
    group by format
    having sum(case when writer = 1 then 1 else 0 end) > 0
    and sum(case when director = 1 then 1 else 0 end) > 0
    and sum(case when editor = 1 then 1 else 0 end) > 0
)

Answer 3

以下是查询：

select * from (
    select format, count(*) as total_count    
    from table    
    group by format    
) as t1    
inner join (    
    select format, count(*) as total_count
    from table
    where writer=1 and director=1 and editor=1
    group by format
) as t2
on t1.format=t2.format and t1.total_count=t2.total_count

主要思想？？计算每种格式有多少行，然后计算出满足条件的行数。