MySQL分组查询返回错误结果

Question

MySQL分组查询返回错误结果

4

我有两个表格（timesheet和tasks），每个表格都包含一个“分配时间”和“实际时间”的小时值列，我想获取这些值的总和。另外，timesheet表格包含一个整数值“staff_id”，对应于任务表格中的“assigned_to”。

任务表格包含：

task_id INT(11)
assigned_to INT(11)
date_start DATE
hrs DECIMAL (10,0)

时间表格包含以下内容：

timesheet_id (int)
name varchar(100)
hours decimal(10,0)
staff_id(INT 11)

我的查询看起来像这样：

    SELECT
        timesheet.staff_id,
        task.assigned_to,
        SUM(task.hrs) AS assigned_hrs,
        timesheet.name,
        SUM(timesheet.hours) AS actual_hours
    FROM timesheet
    INNER JOIN task
    ON timesheet.staff_id = task.assigned_to
    GROUP BY timesheet.name

这将（错误地）导致以下结果：

staff_id       |assigned_to |assigned_hrs    | name.         |  actual_hours |
---------------|------------|----------------|---------------|---------------|
4              |4           | 1364           | John Smith    |52          
2              |2           | 80             | Jane Doe      |14.5        
6              |6           | 454            | Test User 1   |40          
9              |9           | 262            | Test User 2   |4

上面是我想得到的内容，但所有结果都正确，但约翰·史密斯分配的时间翻了一倍。我知道这与“分组陷阱”有关，如此描述：http://wikido.isoftdata.com/index.php/The_GROUPing_pitfall，但我试图理解它时总是眼花缭乱。有人能指点我吗？（再次编辑）如果我只对任务表运行查询：

    SELECT
    task.assigned_to,
    SUM(task.hrs) AS allocated_hrs
    FROM task
    GROUP BY task.assigned_to

它（正确地）导致：

assigned_to | allocated_hrs |
----------------------------
4           |    682
7           |    378
2           |    40
6           |    227
9           |    262

您可以看到用户ID为“4”的John Smith已经翻了一倍（还有ID 6）。

仅在时间表上运行查询：

    SELECT
    timesheet.name,
    SUM(timesheet.hours) AS actual_hours
    FROM timesheet
    GROUP BY timesheet.name

正确的结果是：

    name    |  Actual_hrs
    -------------------------
    Jane Doe   | 19.5
    John Smith | 6.5
    Test User1 | 4
    Test User2 | 5

运行JoachimL提供的查询会产生以下结果：

    staff_id |  assigned_to |   assigned_hrs |  name |  actual_hours
    ----------------------------------------------------------------------
    2   2   40  Jane Doe    19.5
    4   4   24  John Smith  6.5
    4   4   7   John Smith  6.5
    4   4   21  John Smith  6.5
    4   4   210 John Smith  6.5
    4   4   28  John Smith  6.5
    4   4   91  John Smith  6.5
    6   6   14  Test User 1 8
    6   6   91  Test User 1 8
    6   6   28  Test User 1 8
    6   6   3   Test User 1 8
    9   9   24  Test User 2 1
    9   9   91  Test User 2 1
    9   9   56  Test User 2 1

这里有一个演示代码，链接地址为http://sqlfiddle.com/#!2/ef680。

- Lookfar

请编辑您的查询以包括生成不正确结果的数据。 - Gordon Linoff

你觉得按 timesheet.staff_id 进行分组怎么样？也许有两个名字叫 John Smith 的人？ - Joseph B

1

请考虑提供正确的DDL（和/或sqlfiddle），以及所需的结果集。 - Strawberry

同时显示时间表中的样本数据。 - Mihai

3个回答

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

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

- Joachim · Answer 1

没有评论权限...

ID 4和6在时间表中有两行吗？其他的只有一行？那么task.hrs将会翻倍。

像这样的东西应该可以避免这种情况。如果task_id是唯一的，你就不必对它进行求和。(测试数据会有所帮助)

编辑

SELECT
        ts.staff_id,
        task.assigned_to,
        task.hrs AS assigned_hrs,
        ts.name,
        ts.actual_hours
    FROM task
    INNER JOIN (SELECT staff_id, name, SUM(hours) as actual_hours FROM timesheet GROUP BY staff_id, name) as ts
    ON ts.staff_id = task.assigned_to

以上：按员工ID/姓名分组时间表，然后与任务连接，每个任务应该只有一行。

- stomo21 · Answer 2

    SELECT
        timesheet.staff_id,
        task.assigned_to,
        SUM(task.hrs) AS assigned_hrs,
        timesheet.name,
        SUM(timesheet.hours) AS actual_hours
    FROM task
    LEFT JOIN timesheet ON timesheet.staff_id = task.assigned_to
    GROUP BY timesheet.staff_id

尝试使用LEFT JOIN，并确保按照唯一字段进行分组。 "名称" 可能不是唯一的。

注意：LEFT JOIN 将排除任何未分配给任务的时间表。您可以通过从 timesheet LEFT JOIN task 进行 SELECT 来反转此操作。

编辑：请参见此答案：使用 MySQL 查询选择多个求和并在单独列中显示它们抱歉，目前无法评论。

- Strawberry · Answer 3

SELECT x.*
     , SUM(y.hrs) n
  FROM
     ( SELECT t.staff_id
            , t.name
            , SUM(t.hours) actual_hours
         FROM timesheet t
        GROUP 
           BY t.staff_id
     ) x
  JOIN task y
    ON y.assigned_to = x.staff_id
 GROUP
    BY staff_id;

http://sqlfiddle.com/#!2/ef680/14