如何查找列表/集合是否包含在另一个列表中 [英] How to find if a list/set is contained within another list

问题描述

我有一个产品 ID 列表，我想找出哪些订单包含所有这些产品.订单表的结构如下:

I have a list of product IDs and I want to find out which orders contain all those products. Orders table is structured like this:

order_id | product_id
----------------------
1        | 222
1        | 555
2        | 333

显然我可以在 PHP 中使用一些循环来完成它，但我想知道是否有一种优雅的方法可以纯粹在 mysql 中完成它.我理想的幻想查询是这样的:

Obviously I can do it with some looping in PHP but I was wondering if there is an elegant way to do it purely in mysql. My ideal fantasy query would be something like:

SELECT order_id
FROM orders
WHERE (222,555) IN GROUP_CONCAT(product_id)
GROUP BY order_id

有希望还是我应该去读托尔金?:) 另外，出于好奇，如果在 mysql 中不可能，是否还有其他数据库具有此功能?

Is there any hope or should I go read Tolkien? :) Also, out of curiosity, if not possible in mysql, is there any other database that has this functionality?

推荐答案

你已经很近了

SELECT order_id
FROM orders
WHERE product_id in (222,555) 
GROUP BY order_id
HAVING COUNT(DISTINCT product_id) = 2

关于您在关系代数中的出于好奇"问题，这可以通过 师.AFAIK 没有 RDBMS 实现了任何扩展，使其在 SQL 中变得如此简单.

Regarding your "out of curiosity" question in relational algebra this is achieved simply with division. AFAIK no RDBMS has implemented any extension that makes this as simple in SQL.

这篇关于如何查找列表/集合是否包含在另一个列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！