SELECT MAX from COUNT, другие переменные строки не верны

0

Я пытаюсь выбрать МАКС СЧЕТА, группируя их по состоянию (поэтому один максимум для каждого отдельного значения в состоянии). Функция подсчета работает как задумано.

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

Вот результаты,

Изображение 174551

Я использую приведенный ниже оператор 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

Вот результаты, Изображение 174551

3 состояния, которые появляются только один раз в result 1 кажутся хорошими, но для штата Selangor, который появляется дважды в первом результате, были некоторые проблемы.

Запрос SQL выбрал правильное значение MAX (Количество), равное 6, но вместо того, чтобы возвращать идентификатор как 1027, он возвращал идентификатор как 1002, который имеет только 1 в первом результате.

Я пробовал это с разными наборами данных, но я не могу получить подробности о фактической строке MAX (Count).

Вот дизайн базы данных для справки Изображение 174551

SQL Fiddle

Ожидаемый результат - это, (Нужно изменить только вывод второй строки). Изображение 174551Токовый выход

  • 2
    Это проблема n-й строки на группу . Если вы правильно выполните поиск по переполнению стека, вы можете легко получить сотни примеров того же самого. Еще несколько вещей: ваша group by не является допустимым SQL, и в более новых версиях MySQL она потерпит неудачу. Во-вторых, вам не нужно сначала группировать по id а затем создавать вторую группу с помощью подзапроса. Просто сделай прямую группу по state
  • 0
    @MadhurBhaiya Я не знал, как называется проблема. Я сейчас ищу. Я попытался использовать одну группу вместо двух, но дело в том, что я хочу найти большинство услуг, забронированных клиентами, в зависимости от их состояния. Первая группа по группам считается, а вторая группирует их по штатам. Я не получаю желаемых результатов, если объединю их.
Показать ещё 4 комментария
Теги:
join
count
max

1 ответ

2
Лучший ответ

Поскольку вы можете использовать MySQL 8.0, мы можем решить вашу проблему с помощью оконных функций. По разделу state мы определим Row_Number() со строкой, имеющей наибольшее количество, как номер строки 1 и так далее. Теперь нам просто нужно рассмотреть только те строки, где номер строки равен 1, для определенного state.

Кроме того, в вашей попытке GROUP BY не был допустимым SQL. Старые версии MySQL были снисходительны и позволяли это; но новые версии этого не делают, если вы не отключите строгий режим only_full_group_by. Тем не менее, вы не должны отключать его, а вместо этого исправить запрос. Основная суть в том, что при использовании Group By, ваше предложение Select должно содержать только агрегированные столбцы (столбцы)/expression (s) и/или column (s)/expression (s), определенные в предложении Group By. Прочитайте: Ошибка, связанная с only_full_group_by при выполнении запроса в 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     |

Посмотреть на БД Fiddle

Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню