Group By和Filter on 2 distinct values on other column,其中1个值是必需的

select Jn
from your_table
group by Jn
having count(distinct company) >= 2 
   and sum(case when company = 'c1' then 1 else 0 end) > 0

你可以使用 exists :

select t1.Jn 
from table t1
where exists (select 1 from table t2 where t2.jn = t1.jn and t2.company = 'c1')

编辑:

select t1.Jn 
from table t1
group by jn
having count(distinct company) >= 2 and
       sum(case when company = 'c1' then 1 else 0 end) > 0;

使用聚合函数 count()

    select Jn  from T1          
    group by jn
    having count(distinct company)>=2
    and sum(case when company='c1' then 1 else 0 end)>0

这对你的测试数据很有用。我得到所有至少有两个公司的JN,然后与那些有C1公司的JN相交。

SELECT Jn
FROM T1
GROUP BY Jn
HAVING COUNT(Company) >= 2

INTERSECT

SELECT Jn
FROM T1
WHERE Company = 'c1'
GROUP BY Jn;