MySQL SELECT来自OpenCarts数据库的重复行 [英] MySQL SELECT Duplicated rows from OpenCarts DataBase
问题描述
只需与OpenCarts DB一起玩,看看我是否可以学习一些东西. 如果我使用以下 SELECT ,结果将返回重复的行:
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
请注意,即使使用 DISTINCT ,它也是重复的,但是如果我添加 GROUP BY p.product_id ,它将停止重复行.这是最好的解决方案吗?
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?
推荐答案
DISTINCT
删除重复的整行.
使用GROUP BY p.product_id
每个产品ID显示一个1行.
Use GROUP BY p.product_id
to display one 1 row per product id.
注意:如果您按product_id分组,则如果您具有多个产品描述,多个类别或多个类别描述,则查询将为每个查询返回一个随机行.使用 MIN()
或<一个href ="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max" rel ="nofollow"> MAX()
函数来检索单个ID ,或使用 GROUP_CONCAT()
函数以检索所有描述.
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.
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
这篇关于MySQL SELECT来自OpenCarts数据库的重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!