只是玩一下 OpenCart 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它是重复的,但是如果我添加一个按 p.product_id 分组它停止复制行。这是最好的解决方案吗?
DISTINCT
删除重复的整行.
Use GROUP BY p.product_id
每个产品 ID 显示 1 行。
注意:如果按product_id分组,如果您有多个产品描述、多个类别或多个类别描述,则查询将为每个返回随机行。使用MIN() http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_min or MAX() http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max函数来检索单个 ID,或使用GROUP_CONCAT() http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat函数检索所有描述。
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)