时间戳

首页 > 解决方案 > SELECT MAX from COUNT，其他行变量不正确

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

问题描述

我正在尝试选择按状态对它们进行分组的 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

这是结果，

图 1

我使用下面的 SQL 语句尝试选择按状态对它们进行分组的 MAX。

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

这是结果， 图 2

仅出现一次的 3 个状态result 1似乎没问题，但是对于Selangor在第一个结果中出现两次的状态 , 有一些问题。

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

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

这是数据库设计供参考 急诊室

SQL小提琴

预期的结果是这样，（只需更改第二行输出）。 输出 电流输出链路

标签： mysqlsqljoincountmax

解决方案

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

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

 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     |

在 DB Fiddle 上查看

推荐阅读