我有一个公司表,我有一个能力和一个联合表;Companies_Competencies
我试图使所有公司在某个数据集中链接到所有能力的所有公司。
因此,例如,我需要让所有具有1,2和3的公司的公司。
。我从此查询开始:
SELECT companies.id, companies.name FROM companies, companies_competencies
WHERE companies.id = companies_competencies.companies_id
AND companies_competencies.competency_id IN(1,2,3)
GROUP BY companies.id
这为我提供了所有具有1或许多能力的公司,但是我正在努力使所有这些公司都拥有所有能力。
这将为您提供具有所有必要能力的公司ID列表:
select companies_id
from companies_competencies
where competency_id in (1, 2, 3)
group by companies_id
having count(distinct competency_id) = 3 --the # of unique competency IDs
您可以做:
select *
from companies
where id in (
select companies_id
from companies_competencies
where competency_id in (1, 2, 3)
group by companies_id
having count(distinct competency_id) = 3
)
公司只能具有每种能力之一吗?意思是,某个公司只有一个能力ID 1的实例,等等?
如果是这样,由于您已经分组,您可以这样修改您的语句:
SELECT companies.id, companies.name FROM companies, companies_competencies
WHERE companies.id = companies_competencies.companies_id
AND companies_competencies.competency_id IN(1,2,3)
HAVING COUNT(*) = 3
GROUP BY companies.id
只能返回拥有3个记录/能力的公司。