从复杂的MySQL查询中删除重复项

Question

从复杂的MySQL查询中删除重复项

3

所有的项目都存储在com_projects表中。每个项目可以有多个位置，这些位置存储在com_location表中。com_country表只是一个国家名称列表，用于由com_location表中拉入国家名称。

在com_project表中，我有一个category_id字段，可以具有1-4的值。

下面的SELECT查询是我试图计算每个国家分配类别的总次数。

---------------------------------------------------------------
| country | category_1 | category_2 | category_3 | category_4 |
---------------------------------------------------------------
| USA     |     20     |      5     |     3      |     0      |
---------------------------------------------------------------
| UK      |     1      |      12    |     0      |     0      |

etc....

SELECT b.country_id, c.name,
SUM(case when a.category_id = 1 then 1 else 0 end) as category_1,
SUM(case when a.category_id = 2 then 1 else 0 end) as category_2,
SUM(case when a.category_id = 3 then 1 else 0 end) as category_3,
SUM(case when a.category_id = 4 then 1 else 0 end) as category_4
FROM com_project a
Inner JOIN com_location b 
ON a.id = b.project_id
INNER JOIN com_country c
ON c.id = b.country_id
WHERE a.state = 1
AND b.state = 1
GROUP BY b.country_id

如果一个项目在多个国家有多个位置，则只希望该类别增加一次。我遇到的问题是许多项目在同一个国家有多个位置，这会人为地增加结果。

我该如何调整SELECT语句以防止来自同一国家的重复项？

顺便说一下，我尝试在SELECT之后立即添加DISTINCT，但没有帮助。

- user7892262

数据呈现问题应该在应用程序级别的代码中（在这种情况下是PHP）处理。这样更具可扩展性和灵活性，任何其他建议都是愚蠢的。哦，对了，下次请参阅http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query。 - Strawberry

@Strawberry...目前还不清楚PHP如何重新计算聚合值以使其正确。如果您有解决方案，应该将其发布为答案。 - Gordon Linoff

@Strawberry 需要在查询本身中处理，因为正如Gordon所指出的那样，在查询中聚合结果。 - user7892262

为什么我应该为一个对我来说非常简单的SQL查询提供一个MCVE？ - Strawberry

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Answer 1

我认为使用where子句可以过滤掉总是在一个国家的项目：

SELECT l.country_id, c.name,
       SUM(p.category_id = 1) as category_1,
       SUM(p.category_id = 2) as category_2,
       SUM(p.category_id = 3) as category_3,
       SUM(p.category_id = 4) as category_4
FROM com_project p Inner JOIN
     com_location l
     ON p.id = l.project_id INNER JOIN
     com_country c
     ON c.id = l.country_id
WHERE p.state = 1 AND l.state = 1 AND
      EXISTS (SELECT 1
              FROM com_location l2
              WHERE l2.project_id = l.project_id AND l2.country <> l.country
             )
GROUP BY l.country_id, c.name;

这只是检查每个被聚合的项目是否至少有一个其他国家。