我目前有一个相当困惑的查询，它连接来自涉及两个子查询的多个表的数据。我现在需要按 DAY()、WEEK()、MONTH() 和 QUARTER() 对这些数据进行分组。

我有三个表:days、qos 和 employees。一个employee是不言自明的，一个day是一个员工在某一天的表现总结，而qos是一个随机质量检查，每天可以执行多次。

此刻，我正在选择所有employees，并且LEFT JOINing day 和qos，这效果很好。但是，现在，我需要对数据进行分组，以便分割团队或个人在某个日期范围内的表现。

获取这些数据:

员工

id  | name
------------------
1   | Bob Smith

日

id  | employee_id  | day_date   | calls_taken
---------------------------------------------
1   | 1            | 2011-03-01 | 41
2   | 1            | 2011-03-02 | 24
3   | 1            | 2011-04-01 | 35

质量

id   | employee_id  | qos_date   | score
----------------------------------------
1    | 1            | 2011-03-03 | 85
2    | 1            | 2011-03-03 | 95
3    | 1            | 2011-04-01 | 91

如果我开始按 DAY() 分组，我需要看到以下结果:

Day__date  | Day__Employee__id | Day__calls | Day__qos_score
------------------------------------------------------------
2011-03-01 | 1                 | 41         | NULL
2011-03-02 | 1                 | 24         | NULL
2011-03-03 | 1                 | NULL       | 90
2011-04-01 | 1                 | 35         | 91

如您所见，Day__calls 应该是 SUM(calls_taken) 而 Day__qos_score 是 AVG(score)。我已尝试使用与上述类似的方法，但由于在加入其中一个表之前不知道日期，因此它只显示保存了 day 的记录。

有什么办法可以做到这一点，还是我的做法不对？

编辑: 根据要求，这是我到目前为止的想法。但是，它只显示有 day 的日期。

SELECT COALESCE(`day`.day_date, qos.qos_date)      AS Day__date,
      employee.id                                  AS Day__Employee__id,
      `day`.calls_taken                            AS Day__Day__calls,
      qos.score                                    AS Day__Qos__score
FROM   faults_employees `employee`
      LEFT JOIN (SELECT `day`.employee_id                    AS employee_id,
                        SUM(`day`.calls_taken)               AS `calls_in`,
                 FROM   faults_days AS `day`
                 WHERE employee.id = 7
                 GROUP  BY (`day`.day_date)
        ) AS `day`
        ON `day`.employee_id = `employee`.id
      LEFT JOIN (SELECT `qos`.employee_id AS employee_id,
                        AVG(qos.score)   AS `score`
                 FROM   faults_qos qos
                 WHERE employee.id = 7
                 GROUP  BY (qos.qos_date)
        ) AS `qos`
        ON `qos`.employee_id = `employee`.id AND `qos`.qos_date = `day`.day_date
WHERE employee.id = 7
GROUP  BY Day__date
ORDER BY `day`.day_date ASC

最佳答案

我想出的解决方案是这样的:

SELECT
    `date`,
    `employee_id`,
    SUM(`union`.`calls_taken`) AS `calls_taken`,
    AVG(`union`.`score`) AS `score`
FROM ( -- select from union table
    (SELECT -- first select all calls taken, leaving qos_score null
        `day`.`day_date` AS `date`,
        `day`.`employee_id`,
        `day`.`calls_taken`,
        NULL AS `score`
    FROM `employee`
    LEFT JOIN
        `day`
            ON `day`.`employee_id` = `employee`.`id`
    )
    UNION -- union both tables
    (
    SELECT -- now select qos score, leaving calls taken null
        `qos`.`qos_date` AS `date`,
        `qos`.`employee_id`,
        NULL AS `calls_taken`,
        `qos`.`score`
    FROM `employee`
    LEFT JOIN
        `qos`
            ON `qos`.`employee_id` = `employee`.`id`
    )
) `union`
GROUP BY `union`.`date` -- group union table by date

要使 UNION 起作用，我们必须在 day 表和 calls_taken 中设置 qos_score 字段> qos 表中的字段为空。如果我们不这样做，calls_taken 和 score 将被 UNION 语句选入同一列。 在此之后，我从联合表中选择了具有聚合函数 SUM() 和 AVG() 的必填字段，并按 date 联合表中的字段。

关于MySQL 按具有多个连接的日期范围分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6800160/