mysql - 获取与联接表ID匹配或ID列表的子集的记录

This question already has answers here:

How to return rows that have the same column values in MySql
                                
                                    （3个答案）
                                
                        
                                4年前关闭。
            
                    
我有一个名为variants的表，该表通过一个名为option_values_variants的表以多对多关系连接到另一个名为option_values的表。

我想在联接的option_values表中提取所有具有特定ID子集的变量。通过示例更容易演示：

表格数据：

变体

id |      name
--------------
 1 | Tennant
 2 | Smith
 3 | Eccleston
 4 | Hurt

option_values

id | name
---------
80 | blue
81 | red
82 | green
83 | 64 gb
84 | 128 gb
85 | 256 gb
86 | short
87 | long

option_values_variants

variant_id | option_value_id
----------------------------
         1 | 80
         1 | 85
         1 | 86
         2 | 82
         2 | 85
         2 | 87
         3 | 80
         3 | 84
         3 | 86
         4 | 81
         4 | 83
         4 | 86

在此示例中，我想获取所有选项值分别为“ blue”和“ short”的变量（因此，选项值ID为80和86）。

预期的结果应该是变体“ Tennant”和“ Eccleston”，因为这些变体同时具有“ blue”和“ short”，尽管每个都有一个不同的选项值。 “伤害”具有“短裤”但没有“蓝色”，因此应将其排除在外，而“史密斯”则没有。

无论是在Rails上下文中还是在简单的SQL中，就形成此查询而言，我似乎都有些头脑。我似乎无法弄清楚如何指定“仅在联接表的ID匹配或是目标参数列表的子集时才进行联接”。这是我目前的尝试：

@product.variants.joins(:option_values).where(option_values: {id: [80, 86]}).uniq

产生SQL查询：

SELECT DISTINCT `variants`.* FROM `variants`
INNER JOIN `option_values_variants` ON `option_values_variants`.`variant_id` = `variants`.`id`
INNER JOIN `option_values` ON `option_values`.`id` = `option_values_variants`.`option_value_id`
WHERE `variants`.`product_id` = 32 AND `option_values`.`id` IN (80, 86)

由于查询正在执行WHERE ... IN ()，因此不会产生期望的结果。使用上面的示例，我将返回“ Tennant”，“ Eccleston”和“ Hurt”，但是“ Hurt”不应该属于，因为它不包含选项值“ blue”（或选项值id 80）。

如何选择联接表行匹配的记录或给定ID列表的子集？

此外，这可能是由于上述脑部阻滞的结果，但是请承认，我可能无法很好地解释这一点。如果我没有任何道理，请告诉我

最佳答案

您可以使用group by并使用having count(distinct option_values.id) = 2指定结果具有两个选项值

   SELECT
        v.id,
        v.name
    FROM variants v
        INNER JOIN option_values_variants ov
            ON ov.variant_id = v.id
        INNER JOIN option_values o
            ON o.id = ov.option_value_id
    WHERE v.product_id = 32
        AND o.id IN (80, 86)
    GROUP BY
        v.id,
        v.name
    HAVING COUNT(DISTINCT o.id) = 2