gpt4 book ai didi

mysql - 左连接导致重复行

转载 作者:行者123 更新时间:2023-11-29 01:11:51 25 4
gpt4 key购买 nike

我有一个查询可以成功地从我的产品表中获取独特的产品并显示它们。我正在尝试将图标表添加到组合中,其中某些产品可以有多个图标。问题是我试图只使用连接,例如当一个产品有 2 个图标时,我将为该 1 个产品选择 2 行。

这是我的 sql:

    SELECT p.products_image, 
pd.products_name,
p.products_id,
p.products_model,
p.manufacturers_id,
p.products_price,
p2i.icons_id,
p.products_tax_class_id,
IF(s.status, s.specials_new_products_price, NULL) AS
specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) AS
final_price
FROM products p
LEFT JOIN manufacturers m
ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s
ON p.products_id = s.products_id
LEFT JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
LEFT JOIN products_description pd
ON p.products_id = pd.products_id
LEFT JOIN products_to_icon p2i
ON p.products_id = p2i.products_id
WHERE p.products_status = '1'
AND pd.language_id = '1'
AND p2c.categories_id = '36'

最佳答案

在末尾添加一个 GROUP BY 子句。如果你仍然想要所有的图标 id,你可以结合 group_concat

例如:

SELECT p.products_image, 
pd.products_name,
p.products_id,
group_concat(p2i.icons_id separator ",") as icons_ids
(...)
WHERE p.products_status = '1'
AND pd.language_id = '1'
AND p2c.categories_id = '36'
GROUP BY p.products_id

关于mysql - 左连接导致重复行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11351966/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com