MySQL LEFT JOIN重复结果 [英] MySQL LEFT JOIN duplicates results

问题描述

我在实现一个模块中存在一个问题，其中一个project可以属于多个categories.示例:项目"PHP Programmer"属于以下类别:编程，PHP.

I have a problem with implementing a module where one project can belong to multiple categories. Example: project "PHP Programmer" belongs to categories: Programming, PHP.

假设以下查询(选择属于类别1,3,11 的项目):

Assuming the following query (select projects that belong to categories 1,3,11):

SELECT projects.* FROM projects 
    LEFT JOIN pojects_category on projects.id = pojects_category.project_id 
    WHERE pojects_category.category_id IN (1,3,11) and projects.id='94'`

我得到一个相同项目两次返回，因为project_id = 94

I get a the same project returned twice, because there are 2 matches in the project_category table for the project_id = 94

表projects_category模式:

CREATE TABLE IF NOT EXISTS `pojects_category` (
  `project_id` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  KEY `category_id` (`category_id`),
  KEY `project_id` (`project_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `pojects_category` (`project_id`, `category_id`) VALUES
(94, 3),
(94, 1);

我错过了什么吗?

解决方案:使用GROUP BY或DISTINCT

推荐答案

不，这很好.这只是您想使用DISTINCT关键字删除重复项的罕见情况之一.

No, this is fine. This is just one of the rare cases when you want to use the DISTINCT key word to remove duplicates.

在这种情况下，即使查询返回的行多，该逻辑也是正确的.当查询逻辑实际上是错误的时，很多时候可以看到DISTINCT的用法.

In this case this is justified by the fact that the logic of the query is correct, even though it returns more than one row. Many times one can see the usage of DISTINCT when actually the logic of the query is wrong.

旁注:

• 在WHERE子句中使用的除IS NULL/IS NOT NULL之外的任何表引用筛选器，都会使同一表引用上的任何LEFT JOIN都变为INNER JOIN，这与最终结果集的行为有关. (请参见: https://stackoverflow.com/a/15483895/1291428 )

• 您不应使用GROUP BY模拟DISTINCT的效果，原因有两个:

• any filter on a table reference you're using in the WHERE clause other than IS NULL/IS NOT NULL would make any LEFT JOIN on this same table reference turn to an INNER JOIN, as for the final resultset behaviour. (see this: https://stackoverflow.com/a/15483895/1291428)

• you ought not use GROUP BY to simulate the effect of DISTINCT, for 2 reasons:

1/这不是目的. GROUP BY的作用之一是消除重复项，但其主要目的是根据一组特定的条件对行进行分组，以便对其进行一些分析计算/运算.

1/ This is just not the purpose. One of the effects of GROUP BY is to eliminate duplicates, but its main purpose is to group rows according to a certain set of criteria, in order to apply some analytic calculations/operations on them.

2/GROUP BY也会ORDER BY结果(在mysql中)，这不一定是您想要的结果，在这种情况下会减慢执行速度.请确保正确使用引擎提供的功能，从前向兼容性的角度来看，这总是更好的选择. (预计您所包括的内容实际上并非如此)

2/ GROUP BY also ORDER BY the results (in mysql), which is not necessarly what you want and in that case slows down the execution. Please, just ensure appropriate use of what the engines are providing, that's always better from the point of view of forward compatibility. (anticipating that what you include as granted is actually not)

致谢.

这篇关于MySQL LEFT JOIN重复结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！