列名 CandId 表明你有一个 Cand 包含人员的表。然后，您可以从该表中选择并使用 IN 或者 EXISTS :

select *
from cand
where candid in (select candid from cand_languages where speak = 'English')
  and candid in (select candid from cand_languages where speak = 'Spanish')
order by candid;

对于大型表，您可以提供索引以快速查找：

create index idx on cand_languages(speak, candid);

SELECT CandId
  FROM Candidate
 WHERE Speak in ('English','Spanish')
 GROUP BY CandId
HAVING COUNT(CandId) = 2

它返回所有candid，其中有2个记录匹配“english”或“spansian”。如果搜索更多语言，having子句中的语言数也会更改为2。
更新（感谢spencer7593的有效评论）：如果candid+speak组合不是唯一的，则必须使用：

SELECT CandId
  FROM Candidate
 WHERE Speak in ('English','Spanish')
 GROUP BY CandId
HAVING COUNT(DISTINCT Speak) = 2

用having子句进行聚合是回答这些“集合内的集合”问题的一种非常通用的方法。对于英语和西班牙语：

select candid
from t
group by candid
having sum(language = 'English') > 0 and
       sum(language = 'Spanish') > 0;

如果你不想学西班牙语：

having sum(language = 'English') > 0 and
       sum(language = 'Spanish') = 0;

nuahatl和amdo，但不是普通话：

having sum(language = 'Nuahatl') > 0 and
       sum(language = 'Amdo') > 0 and
       sum(language = 'Mandarin') = 0;

您可以使用candid将表自身连接起来：

SELECT
    DISTINCT t1.CandId
FROM
    `table` t1
    JOIN `table` t2 ON t2.CandId = t1.CandId
WHERE
    t1.Speak = 'English'
    AND t2.Speak = 'Spanish';

看到了吗http://sqlfiddle.com/#!9/97635c/3为工作示例

您可以使用case+sum对每个候选人的语言进行计数，然后选择only having count=2：

select CandId
from
(
select CandId, 
sum( case speak
      when 'English' then 1
      when 'Spanish' then 1
      else 0
    end)  countlang
from Table
group by CandId
)x
where countlang=2