结合使用SUM()，GROUP_BY()和LEFT_JOIN()会返回错误的结果:如何解决? [英] Combining SUM(), GROUP_BY() and LEFT_JOIN() returns incorrect results: how to fix?

问题描述

我正在编写一个查询，该查询应每天/每月/每年返回多个用户的汇总小时数.

I am writing a query that should return aggregated hours for multiple users per day/month/year.

表看起来像这样:

+------------------------------------------+
| id | entity_id | minutes | person | date |
+------------------------------------------+

输出应该的外观:

How output should look:

+----------------------------+
| year | month | day | hours |
| 2008 | 12    | 1   | 30    |
| 2008 | 12    | 2   | 40    |
| 2008 | 12    | 3   | 23    |
+----------------------------+

相反，由于left join导致返回的行，hours通常会更多.

Instead, the hours are often a lot more due to the returned rows caused by the left join.

问题是我需要根据链接到相应实体的标签来查询此表.当我加入两个表(提供链接的tag_entity和提供实际标签名称的tags)时，由于返回的结果太多，我的SUM()不再起作用.

The problem is that I need to query this table based on the tags that are linked to the corresponding entities. When I join the two tables (tag_entity that provides the link and tags that provides the actual tag names) my SUM() no longer works since there are too many results being returned.

查询:

select 
    date_format(from_unixtime(date), '%Y-%m-%d') as myDate,
    ROUND(SUM(time) / 60,1) as hours

from time h

left join tag_entity te on te.entity_id = h.entity_id
left join tags t on t.tag_id = te.tag_id

where (t.tag_name NOT IN ('foo', 'bar', 'baz') OR t.tag_name IS NULL) 

group by
    myDate

order by
    hours DESC, myDate ASC

我该如何解决?

以下是tag和tag_entity的架构:

Tag:

+----------+-------------+
| Field    | Type        |
+----------+-------------+
| tag_id   | int(11)     |
| tag_name | varchar(50) |
+----------+-------------+

和tag_entity:

+-----------+---------+
| Field     | Type    |
+-----------+---------+
| id        | int(11) |
| tag_id    | int(11) |
| entity_id | int(11) |
+-----------+---------+

推荐答案

GROUP BY对结果进行分组，而不是对表行进行单独分组.

GROUP BY groups the results, not the table rows individually.

根据您的评论仅返回时间表中未链接到这些标记之一的行:

SELECT 
    date_format(from_unixtime(date), '%Y-%m-%d') as myDate,
    ROUND(SUM(time) / 60,1) as hours
FROM `time` h
  LEFT JOIN (
    SELECT DISTINCT te.entity_id
    FROM tag_entity te
      LEFT JOIN tags t on t.tag_id = te.tag_id
    WHERE te.entity_id IS NOT NULL AND t.tag_name IN ('foo', 'bar', 'baz')
  ) g ON h.entity_id = g.entity_id
WHERE g.entity_id IS NULL
group by
    myDate

order by
    hours DESC, myDate ASC

这篇关于结合使用SUM()，GROUP_BY()和LEFT_JOIN()会返回错误的结果:如何解决?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！