从count中选择max，其他行变量不正确

acruukt9 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(360)

我试图选择一个按状态分组的计数的最大值（因此状态中每个不同的值有一个最大值）。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

这是结果，

三种状态，每种状态只出现一次 result 1 看起来不错，但对国家来说， Selangor 在第一个结果中出现两次的出现了一些问题。
sql查询选择了正确的最大值（count），即6，但它没有将id返回为1027，而是将id返回为1002，在第一个结果中，count仅为1。
我尝试了不同的数据集，但我似乎无法获得实际的max（count）行的详细信息。
以下是数据库设计参考

sql小提琴
预期结果是这样的（只需要更改第二行输出）。

电流输出环节

sql mysql Join count Max

来源：https://stackoverflow.com/questions/53142770/select-max-from-count-other-row-variables-are-not-correct

1条答案

按热度按时间

c8ib6hqw1#

既然您可以使用mysql 8.0，我们就可以使用窗口函数来解决您的问题。在一个 state ，我们将确定 Row_Number() 具有最高计数的行作为行号1，依此类推。现在，我们只需要考虑那些行号为1的行，对于一个特定的 state .
另外，在你的尝试中， GROUP BY 不是有效的sql。mysql的旧版本是宽容的，允许使用它；但新版本不会，除非你关闭严格的 only_full_group_by 模式。但是，不应禁用它，而应修复查询。基本要点是，当使用 Group By ，您的 Select 子句只能包含聚合列/表达式和/或中定义的列/表达式 Group By 条款。do read:在mysql中执行查询时，只与\u full \u group \u 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视图

赞(0）回复(0）举报 2021-06-18

我来回答

从count中选择max，其他行变量不正确

1条答案

相关问题

热门标签

最新问答