MySQL SELECT OpenCarts 数据库中的重复行

发布于 2024-10-17 13:16:35 字数 705 浏览 2 评论 0原文

只是玩一下 OpenCarts DB，看看我是否能学到一些东西。如果我使用以下SELECT，结果会返回重复的行：

SELECT DISTINCT
p.product_id AS pid,
p.model AS modelo,
SUBSTRING(p.model,1,25) AS substr_modelo,
p.image AS foto,
p.price AS preco,
pd.name AS nome,
cd.name AS category
FROM product p
LEFT JOIN product_description pd ON p.product_id = pd.product_id
LEFT JOIN product_to_category p2c ON p.product_id = p2c.product_id
LEFT JOIN category_description cd ON p2c.category_id = cd.category_id
WHERE pd.name LIKE _utf8 'laser%' collate utf8_unicode_ci
ORDER BY p.product_id DESC

请注意，即使使用DISTINCT，它也会重复，但如果我添加GROUP BY p.product_id 它停止复制行。这是最好的解决方案吗？

原文

Just playing with OpenCarts DB to see if I can lear something.
If I use the following SELECT the result returns duplicated rows:

SELECT DISTINCT
p.product_id AS pid,
p.model AS modelo,
SUBSTRING(p.model,1,25) AS substr_modelo,
p.image AS foto,
p.price AS preco,
pd.name AS nome,
cd.name AS category
FROM product p
LEFT JOIN product_description pd ON p.product_id = pd.product_id
LEFT JOIN product_to_category p2c ON p.product_id = p2c.product_id
LEFT JOIN category_description cd ON p2c.category_id = cd.category_id
WHERE pd.name LIKE _utf8 'laser%' collate utf8_unicode_ci
ORDER BY p.product_id DESC

Note that even using DISTINCT it is duplicated, but if I add an GROUP BY p.product_id it stops duplicating the rows. Is it the best solution?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只是在用心讲痛 2024-10-24 13:16:35

DISTINCT 删除重复的整行。

使用 GROUP BY p.product_id 为每个产品 ID 显示 1 行。

注意：如果按product_id分组，如果您有多个产品描述、多个类别或多个类别描述，则查询将为每个返回随机行。使用 MIN() 或 MAX( ) 函数来检索单个 ID，或使用 GROUP_CONCAT() 函数检索所有描述。

例子

SELECT
  p.product_id AS pid,
  p.model AS modelo,
  SUBSTRING(p.model,1,25) AS substr_modelo,
  p.image AS foto,
  p.price AS preco,
  GROUP_CONCAT(pd.name) AS nome,
  GROUP_CONCAT(cd.name) AS category
FROM product p
  LEFT JOIN product_description pd ON p.product_id = pd.product_id
  LEFT JOIN product_to_category p2c ON p.product_id = p2c.product_id
  LEFT JOIN category_description cd ON p2c.category_id = cd.category_id
  WHERE pd.name LIKE _utf8 'laser%' collate utf8_unicode_ci
GROUP BY p.product_id
ORDER BY p.product_id DESC

DISTINCT removes duplicate entire rows.

Use GROUP BY p.product_id to display one 1 row per product id.

Note: If you group by product_id, if you have multiple product description's, multiple categories, or multiple category description's The query will return a random row for each. Use the MIN() or MAX() functions to retrieve single ID's, or use the GROUP_CONCAT() function to retrieve all the description.

Example

SELECT
  p.product_id AS pid,
  p.model AS modelo,
  SUBSTRING(p.model,1,25) AS substr_modelo,
  p.image AS foto,
  p.price AS preco,
  GROUP_CONCAT(pd.name) AS nome,
  GROUP_CONCAT(cd.name) AS category
FROM product p
  LEFT JOIN product_description pd ON p.product_id = pd.product_id
  LEFT JOIN product_to_category p2c ON p.product_id = p2c.product_id
  LEFT JOIN category_description cd ON p2c.category_id = cd.category_id
  WHERE pd.name LIKE _utf8 'laser%' collate utf8_unicode_ci
GROUP BY p.product_id
ORDER BY p.product_id DESC

回复收藏 0 原文

~没有更多了~