我创建了一个名为recommendations
的表,如下所示:
+----+---------------+---------------------+
| id | name | elements_to_announce |
+----+---------------+---------------------+
| 1 | Recomanation1 | 2 |
| 2 | Recomanation1 | 1,2 |
+----+---------------+---------------------+
另一个名为
products
的表如下所示:+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Low fat croissant |
| 2 | Yupi bread |
+----+-------------------+
现在,我想用这两个表创建一个联接,该联接获取
recommendations
的信息,但将elements_to_announce
替换为产品名称。它必须是这样的:
+----+---------------+---------------------------------------+
| id | name | elements_to_announce |
+----+---------------+---------------------------------------+
| 1 | Recomanation1 | Yupi bread |
| 2 | Recomanation1 | Low fat croissant,Yupi bread |
+----+---------------+---------------------------------------+
我进行了很多测试,几乎可以正常工作的代码是:
SELECT
r.id AS recommendation_id,
GROUP_CONCAT(p.id) AS product_id
FROM products AS p
JOIN recommendations AS r
GROUP BY r.id
此代码的问题是它返回所有产品名称。
编辑:
我的“差不多”查询的结果:
+----+---------------+---------------------------------------+
| id | name | elements_to_announce |
+----+---------------+---------------------------------------+
| 1 | Recomanation1 | Low fat croissant,Yupi bread |
| 2 | Recomanation1 | Low fat croissant,Yupi bread |
+----+---------------+---------------------------------------+
最佳答案
问题是,您似乎在使用关系数据库时不使用它。表recommendations
违反了所谓的first normal form的条件。我建议将其分为两个表:recommendations
:id
,name
recommendationContents
:recommendation_id
,product_id
这就是查询的样子:
SELECT r.id, r.name, GROUP_CONCAT(p.name)
FROM recommendationContents AS rc
JOIN recommendations AS r ON rc.recommendation_id = r.id
JOIN products AS p ON rc.product_id = p.id
GROUP BY r.id, r.name
关于mysql - 在内部联接中访问具有ID列表的字段信息,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39081340/