我有一个表T1
Jn company
X1 c1
X1 c2
X1 c3
Y2 c1
Y2 c2
Y2 c3
Z3 c2
Z3 c3
Z3 c4
我想按 Jn 分组并仅过滤那些至少有 2 家不存在的公司并且其中 1 家应该是 c1 的记录。
期望的结果:
X1
Y2
我像这样尝试
select Jn from T1
group by Jn
having -----
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
这对我适用于您的测试数据。 我得到所有拥有至少 2 家公司的 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;