MySQL 匹配 IN 子句中的所有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11636061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Matching all values in IN clause
提问by Dima Knivets
Is there a way to ensure all values in an INclause are matched?
有没有办法确保IN子句中的所有值都匹配？
Example:
例子：
I can use IN as: IN (5,6,7,8).
我可以使用如下：IN (5,6,7,8)。
I need it to work like an ANDacross multiple rows.
我需要它像AND跨多行一样工作。
UPDATE:I need this to list companies from db that fit specified parameters. Companies and taxonomy are MANY TO MANY relation. I'm using Yii framework. And this is the code of my controller:
更新：我需要这个来从数据库中列出符合指定参数的公司。公司和分类法是多对多的关系。我正在使用 Yii 框架。这是我的控制器的代码：
public function actionFilters($list)
{
$companies = new CActiveDataProvider('Company', array(
'criteria' => array(
'condition'=> 'type=0',
'together' => true,
'order'=> 'rating DESC',
'with'=>array(
'taxonomy'=>array(
'condition'=>'term_id IN ('.$list.')',
)
),
),
));
$this->render('index', array(
'companies'=>$companies,
));
}
回答by RedFilter
You can do something like this:
你可以这样做：
select ItemID
from ItemCategory
where CategoryID in (5,6,7,8) <-- de-dupe these before building IN clause
group by ItemID
having count(distinct CategoryID) = 4 <--this is the count of unique items in IN clause above
If you provide your schema and some sample data, I can provide a more relevant answer.
如果您提供您的架构和一些示例数据，我可以提供更相关的答案。
回答by Ryan
SELECT ItemID
FROM ItemCategory
WHERE (
(CategoryID = 5) OR
(CategoryID = 6) OR
(CategoryID = 7) OR
(CategoryID = 8)
)
GROUP BY ItemID
HAVING COUNT(DISTINCT CategoryID) = 4
