如何查找列表/集合是否正好在另一个列表中 [英] How to find if a list/set is exactly within another list
问题描述
我的问题基本上是这个问题的扩展 - 如何查找列表/集合是否包含在另一个列表中
My question is basically an extension of this question - How to find if a list/set is contained within another list
我有相同的数据,但有更多完全相同的产品 -
I have the same data, but with more exact same products -
order_id | product_id
----------------------
1 | 222
1 | 555
2 | 333
2 | 222
2 | 555
我想查找订单完全指定了产品 ID 的订单.
I want to find the order(s) where the order has EXACTLY given product ids.
例如:如果我搜索 222 &555,我应该得到 order_id 1 作为输出,因为它只有那 2 个确切的产品 ID.并且不应将订单 ID 2 作为输出,因为它有一个额外的 333 产品 ID.
For example: If I search for 222 & 555, I should get order_id 1 as output since it only have those 2 exact product ids. And should not get order id 2 as output since it has an additional 333 product id.
推荐答案
您仍然可以使用 have
,但要对每个产品进行测试——以及是否没有任何其他产品:
You can still use having
, but test for each product -- and for the absence of any other product:
SELECT o.order_id
FROM orders o
GROUP BY o.order_id
HAVING SUM( product_id = 222 ) > 0 AND
SUM( product_id = 555 ) > 0 AND
SUM( product_id NOT IN (222, 555) ) = 0 ;
请注意,这里使用 MySQL 简写,其中布尔表达式被视为数字,1
表示真,0
表示假.标准语法是:
Note that this uses the MySQL shorthand, where boolean expressions are treated as numbers with 1
for true and 0
for false. The standard syntax is:
HAVING SUM( CASE WHEN product_id = 222 THEN 1 ELSE 0 END) > 0 AND
SUM( CASE WHEN product_id = 555 THEN 1 ELSE 0 END ) > 0 AND
SUM( CASE WHEN product_id NOT IN (222, 555) THEN 1 ELSE 0 END ) = 0 ;
这篇关于如何查找列表/集合是否正好在另一个列表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!