mysql - 选择多对多关系的另一端

Question

背景

我有三个表:(SQL fiddle :http://sqlfiddle.com/#!2/f7b33/11)

产品

+----+-----------+
| id |  product  |
+----+-----------+
|  1 | product_1 |
|  2 | product_2 |
|  3 | product_3 |
+----+-----------+

products_features

+------------+------------+
| product_id | feature_id |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          3 |
|          3 |          4 |
+------------+------------+

特征

+----+-----------+
| id |  feature  |
+----+-----------+
|  1 | feature_1 |
|  2 | feature_2 |
|  3 | feature_3 |
|  4 | feature_4 |
+----+-----------+

然后我这样选择:

SELECT products.product,
       GROUP_CONCAT(features.feature) AS features
FROM   products
       LEFT JOIN products_features
              ON product_id = products.id
       LEFT JOIN features
              ON products_features.feature_id = features.id
GROUP  BY products.id

得到类似的东西:

+-----------+-----------------------------------------+
|  product  |                features                 |
+-----------+-----------------------------------------+
| product_1 | feature_1,feature_2,feature_3,feature_4 |
| product_2 | feature_1,feature_3                     |
| product_3 | feature_4                               |
+-----------+-----------------------------------------+

---

问题

所以，一切都很好，但是，我想做的是只拥有具有 feature_1 和 feature_3 的东西，同时仍然获得其他功能。

换句话说，我想编写一个查询来获取我的信息:

+-----------+-----------------------------------------+
|  product  |                features                 |
+-----------+-----------------------------------------+
| product_1 | feature_1,feature_2,feature_3,feature_4 |
| product_2 | feature_1,feature_3                     |
+-----------+-----------------------------------------+

我试过:

SELECT products.product,
       GROUP_CONCAT(features.feature) AS features
FROM   products
       LEFT JOIN products_features
              ON product_id = products.id
       RIGHT JOIN features
              ON products_features.feature_id = features.id AND features.feature in ('feature_1','feature_3')

GROUP  BY products.id

当然我得到:

+-----------+---------------------+
|  product  |      features       |
+-----------+---------------------+
| (null)    | feature_4,feature_2 |
| product_1 | feature_1,feature_3 |
| product_2 | feature_3,feature_1 |
+-----------+---------------------+

因此，虽然我现在知道 product_1 和 product_2 具有这些功能，但我看不到它们具有的其余功能。

什么查询会允许我指定 feature_1 和 feature_3 并获得以下响应？

+-----------+-----------------------------------------+
|  product  |                features                 |
+-----------+-----------------------------------------+
| product_1 | feature_1,feature_2,feature_3,feature_4 |
| product_2 | feature_1,feature_3                     |
+-----------+-----------------------------------------+

Answer 1

我认为最通用的方法是使用 having 子句:

SELECT products.product,
       GROUP_CONCAT(features.feature) AS features
FROM   products
       LEFT JOIN products_features
              ON product_id = products.id
       LEFT JOIN features
              ON products_features.feature_id = features.id
GROUP  BY products.id
HAVING sum(features.feature = 'feature_1') > 0 and
       sum(features.feature = 'feature_3') > 0;

having 语句中的每个子句都在计算给定特征出现的次数。 and 要求这两个特征都在最终结果集中。

编辑:

鉴于您的陈述结构，您还可以:

HAVING find_in_set('feature_1', features) > 0 and
       find_in_set('feature_3', features) > 0;

这是有效的，因为您正在生成一个包含功能列表的列，并且您使用逗号作为该列表的分隔符。