mysql - SELECT MAX from COUNT，其他行变量不正确

Question

我正在尝试选择按州对它们进行分组的 COUNT 的最大值(因此州中每个不同值都有一个最大值)。计数函数按预期工作。

SELECT c.id, c.name, t.name as type, COUNT(*) as count, c.state 
FROM bookings_facilities f 
JOIN bookings b 
ON b.id = f.booking_id 
JOIN clients c 
ON c.id = b.client_id 
JOIN client_types t 
ON c.type = t.id 
WHERE t.name = "School" 
GROUP BY c.id

这是结果，

我使用下面的 SQL 语句来尝试选择按状态分组的最大计数。

SELECT *, MAX(z.count) 
FROM (SELECT c.id, c.name, t.name as type, COUNT(*) as count, c.state 
    FROM bookings_facilities f 
    JOIN bookings b 
    ON b.id = f.booking_id 
    JOIN clients c 
    ON c.id = b.client_id 
    JOIN client_types t 
    ON c.type = t.id 
    WHERE t.name = "School" 
    GROUP BY c.id) z
GROUP BY z.state

这是结果，

在结果 1 中仅出现一次的 3 个州属似乎没问题，但对于在第一个结果中出现两次的 雪兰莪 州属来说，有一些问题问题。

SQL 查询选择了正确的 MAX(Count)，即 6，但返回的 id 不是 1027，而是 1002，第一个结果中只有 count 为 1。

我已经尝试使用不同的数据集，但我似乎无法获取实际 MAX(Count) 行的详细信息。

这里是数据库设计供引用

SQL Fiddle

预期结果是这样的(只需更改第二行输出)。 Current Output Link

Answer 1

自 you can use MySQL 8.0 ，我们可以使用 Window Functions 解决您的问题。在状态的分区上，我们将确定 Row_Number()计数最高的行为行号 1，依此类推。现在，对于特定的状态，我们只需要考虑行号为 1 的那些行。

此外，在您的尝试中，GROUP BY 不是有效的 SQL。旧版本的 MySQL 很宽松并允许这样做；但较新的版本不会，除非您关闭严格的 only_full_group_by 模式。但是，您不应禁用它，而应修复查询。基本要点是，使用 Group By 时，您的 Select 子句应仅包含聚合列/表达式和/或列/Group By 子句中定义的表达式。请阅读:Error related to only_full_group_by when executing a query in MySql

 SELECT dt2.id,
       dt2.NAME,
       dt2.state,
       dt2.type,
       dt2.count
FROM   (SELECT dt1.id,
               dt1.NAME,
               dt1.state,
               dt1.type,
               dt1.count,
               Row_number()
                 OVER (
                   partition BY dt1.state
                   ORDER BY dt1.count DESC) AS row_num
        FROM   (SELECT c.id,
                       c.NAME,
                       c.state,
                       t.NAME   AS type,
                       Count(*) AS count
                FROM   bookings_facilities AS f
                       JOIN bookings AS b
                         ON b.id = f.booking_id
                       JOIN clients AS c
                         ON c.id = b.client_id
                       JOIN client_types AS t
                         ON c.type = t.id
                WHERE  t.NAME = 'School'
                GROUP  BY c.id,
                          c.NAME,
                          c.state,
                          type) AS dt1) AS dt2
WHERE  dt2.row_num = 1  

结果:

| id   | NAME                                     | state     | type   | count |
| ---- | ---------------------------------------- | --------- | ------ | ----- |
| 1006 | Holy Child Montessory School Of Fairview | Manila    | School | 1     |
| 1027 | Sri Kuala Lumpur                         | Selangor  | School | 6     |
| 1010 | Singapore American School                | Singapore | School | 1     |
| 1015 | Keika Junior & Senior High School        | Tokyo     | School | 1     |

View on DB Fiddle