你似乎发现下面有条件的拒绝

select session_id from table
where fault IN ('a', 'b', 'c', 'd')
group by session_id
having count( distinct fault)=4

下面是bigquery标准sql

# standardSQL

SELECT AS VALUE ANY_VALUE(t)
FROM `project.dataset.table` t  
WHERE fault IN ('a', 'b', 'c', 'd')
GROUP BY session_id

它返回所选行中的所有列
如果您需要选择last的逻辑（比如说按错误排序），那么您可以使用下面的版本

# standardSQL

SELECT AS VALUE ARRAY_AGG(t ORDER BY fault DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.table` t  
WHERE fault IN ('a', 'b', 'c', 'd')
GROUP BY session_id

如果你特别想要故障“c”，那么

select (array_agg(t order by (fault = 'c') desc limit 1))[ordinal(1)].*
from t
where fault in ('a', 'b', 'c', 'd')
group by session_id
having count(distinct fault) = 4;

如果会话中没有重复出现故障（如示例数据中所示），则可以使用：

select t.*
from (select t.*,
             count(*) over () as cnt
      from t
      where fault in ('a', 'b', 'c', 'd')
     ) t
where cnt = 4 and fault = 'c';

如果它们可以重复，你可以使用 count(distinct) 取而代之的是：

select t.*
from (select t.*,
             count(distinct fault) over () as cnt
      from t
      where fault in ('a', 'b', 'c', 'd')
     ) t
where cnt = 4 and fault = 'c';

很多其他的好答案，但这可以用基本原则来解决：选择有错误c的行，选择有4个错误的行（或者任何你需要的标准），然后加入。

with faults_c as ( 
  select * from table where fault = c
),
faults_4 as (
  select session_id from table 
  group by 1 
  having count(distinct fault) = 4
)
select *
from faults_c
inner join faults_4 using(session_id)