如何选择行时列必须满足多个值？

Question

例如，我有一个表

name | ability 

kevin|say 
kevin|scream 
nike |say 

我想只得到响应凯文，寻找say和scream时。参数计数可能会改变。

Answer 1

您可以group by和having做到这一点：

SELECT name 
FROM t 
WHERE ability in ('say', 'scream') 
GROUP BY name 
HAVING COUNT(*) = 2; 

Answer 2

一种方式来做到这一点是：

SELECT * FROM YourTableName WHERE name='kevin' AND ability IN ('say','scream'); 

另一种方式来做到这一点：

SELECT * FROM YourTableName WHERE name='kevin' AND (ability='say' || ability='scream'); 

祝你好运！

Answer 3

这应该工作：

SELECT name FROM tablename WHERE ability IN ('say', 'scream') GROUP BY name 

或者：

SELECT DISTINCT name FROM tablename WHERE ability IN ('say', 'scream') 

编辑：哦，你要寻找的是具有都能力名字......然后，你可以这样做：

SELECT DISTINCT name from tablename WHERE name IN (SELECT name FROM tablename WHERE ability = 'say') AND name IN (SELECT name FROM tablename WHERE ability = 'scream') 

或者更好的是，你可以添加一个HAVING子句第一个查询：

SELECT name FROM tablename WHERE ability IN ('say', 'scream') GROUP BY name HAVING count(*) = 2