mysql - 如何在 MySQL 中正确分组

Question

我在 mysql 中遇到 group by 问题。以下是从我的测试表中获取所有事件/描述的示例查询/结果：

    SELECT 
        customer.id AS id,
        customer.first_name AS firstName,
        customer.middle_names AS middleNames,
        customer.last_name AS lastName,
        office.handler AS handler,
        office.handled AS handled,
        event.day AS eventDay,
        reason.description,
    FROM event event
    LEFT JOIN alert alert on event.id = alert.event_id
    LEFT JOIN reason reason on alert.id = reason.alert_id
    LEFT JOIN customer on customer.id = alert.customer_id
    LEFT JOIN (
        SELECT identity, handler, handled, MAX(created) AS created
        FROM office_handling GROUP BY identity
    ) office ON (customer.identity_id = handling.identity)
    ORDER BY eventDay DESC

+----------+-----------+-------------+----------+---------+---------+---------------------+-------------------
| id       | firstName | middleNames | lastName | handler | handled | eventDay            | description      |
+----------+-----------+-------------+----------+---------+---------+---------------------+-------------------
|      137 | Test2     | Somebody    | Last2    | NULL    | NULL    | 2021-06-25 00:00:00 | Some description |    
|      137 | Test2     | Somebody    | Last2    | NULL    | NULL    | 2021-06-25 00:00:00 | Some description |                     
|      136 | Test1     | Someone     | Last1    | NULL    | NULL    | 2021-06-25 00:00:00 | Some description |   
|      136 | Test1     | Someone     | Last1    | NULL    | NULL    | 2021-06-25 00:00:00 | Some description | 
|      137 | Test2     | Somebody    | Last2    | NULL    | NULL    | 2021-04-15 00:00:00 | Some description |   
|      137 | Test2     | Somebody    | Last2    | NULL    | NULL    | 2021-04-15 00:00:00 | Some description | 
|      136 | Test1     | Someone     | Last1    | NULL    | NULL    | 2021-04-15 00:00:00 | Some description | 
|      136 | Test1     | Someone     | Last1    | NULL    | NULL    | 2021-04-15 00:00:00 | Some description |
+----------+-----------+-------------+----------+---------+---------+---------------------+-------------------

将此查询与 group by 一起使用时：

    SELECT 
        customer.id AS id,
        customer.first_name AS firstName,
        customer.middle_names AS middleNames,
        customer.last_name AS lastName,
        office.handler AS handler,
        office.handled AS handled,
        event.day AS eventDay,
        COUNT(reason.description) as count,
    FROM event event
    LEFT JOIN alert alert on event.id = alert.event_id
    LEFT JOIN reason reason on alert.id = reason.alert_id
    LEFT JOIN customer on customer.id = alert.customer_id
    LEFT JOIN (
        SELECT identity, handler, handled, MAX(created) AS created
        FROM office_handling GROUP BY identity
    ) office ON (customer.identity_id = handling.identity)
    GROUP BY id ORDER BY eventDay DESC

结果我得到：

+----------+-----------+-------------+----------+---------+---------+---------------------+--------
| id       | firstName | middleNames | lastName | handler | handled | eventDay            | count |
+----------+-----------+-------------+----------+---------+---------+---------------------+--------
|      136 | Test1     | Someone     | Last1    | NULL    | NULL    | 2021-06-25 00:00:00 | 8     |   
+----------+-----------+-------------+----------+---------+---------+---------------------+--------

我想要得到的是：

+----------+-----------+-------------+----------+---------+---------+---------------------+--------
| id       | firstName | middleNames | lastName | handler | handled | eventDay            | count |
+----------+-----------+-------------+----------+---------+---------+---------------------+--------
|      136 | Test1     | Someone     | Last1    | NULL    | NULL    | 2021-06-25 00:00:00 | 4     |   
+----------+-----------+-------------+----------+---------+---------+---------------------+--------
|      137 | Test2     | Somebody    | Last2    | NULL    | NULL    | 2021-06-25 00:00:00 | 4     |   
+----------+-----------+-------------+----------+---------+---------+---------------------+--------

我只是似乎无法正确修改查询以获得想要的结果。任何帮助如何做到这一点？

标签： mysqlgroup-by