Блог

Главная - Вопросы по программированию - Правильная замена цикла на сервере MySQL

Правильная замена цикла на сервере MySQL

#mysql

#mysql

Вопрос:

У меня есть эта таблица:

введите описание изображения здесь

И я хочу найти уровень, который появляется чаще всего для каждого возраста, например, для 18 лет уровень, который чаще всего появляется, — «FR», а для 19 лет — «SO».

Моя попытка:

 SELECT X.AGE, X.LEVEL FROM 
( SELECT S.AGE, S.LEVEL FROM STUDENT S WHERE S.AGE = 18) AS X 
GROUP BY X.LEVEL 
ORDER BY COUNT(*) DESC LIMIT 1;

Я получаю такой результат:

введите описание изображения здесь

Как сделать итерацию для всех разных возрастов, которые они есть в этом списке SELECT DISTINCT S.AGE FROM STUDENT S; , зная, что мне запрещено использовать цикл while.

Ответ №1:

До MySQL 8.0 вам пришлось бы использовать самосоединение.

Сначала вы извлекаете возраст и уровень с подсчетами.

 SELECT AGE, LEVEL, COUNT(*) AS C FROM STUDENT GROUP BY AGE, LEVEL

Это дает вам, скажем,

 18 FR 2
18 SR 5
18 XX 5
19 FR 1

Исходя из этого, вы выбираете максимальное значение для каждого возраста, но вам нужна только одна строка, и оба SR и XX имеют максимальное значение с одинаковым значением 5, когда возраст равен 18. В противном случае вы могли бы использовать самосоединение, сначала выбрав «18 5» с помощью MAX(), а затем снова присоединившись, чтобы получить УРОВЕНЬ, соответствующий (18, 5) — только здесь есть два уровня, которые будут соответствовать этому. Конечно, вы могли бы использовать другой МАКСИМУМ.

 SELECT A1.AGE, MAX(A2.LEVEL) FROM
  ( SELECT AGE, MAX(C) AS M FROM ( the query above ) AS A0 GROUP BY AGE ) AS A1
  JOIN
  ( SELECT AGE, MAX(C) AS M FROM ( the query above ) AS A0 GROUP BY AGE ) AS A2
ON (A1.AGE = A2.AGE AND A1.M = A2.M)

Или вы могли бы использовать хак, чтобы ограничить количество подзапросов и самосоединений:

 SELECT AGE, SUBSTRING_INDEX(MAX(CONCAT(LPAD(C, 5, '0'), ':', LEVEL), ':', -1)) AS LEVEL FROM (
    SELECT AGE, LEVEL, COUNT(*) AS C FROM STUDENT GROUP BY AGE, LEVEL
) AS INT GROUP BY AGE

Это сложное выражение сначала объединит числа и уровень в одну строку, так что «1 FR» и «15 SR» станут «00001: FR» и «00015: SR», что позволяет сравнивать в лексикографическом порядке. Затем MAX извлечет максимум в алфавитном порядке, который теперь также совпадает с порядком чисел, используя УРОВЕНЬ, когда числа равны. Итак, теперь вы получите

 18   00005:XX

и, наконец, SUBSTRING_INDEX примет последний элемент строки, разделенный двоеточием, поэтому желаемое значение «XX».

Комментарии:

1. Спасибо вам за ваше время и объяснение, действительно полезное и отлично решающее мою проблему.

Ответ №2:

Вы можете это сделать, если вы GROUP BY AGE, LEVEL и используете FIRST_VALUE() функцию window:

 SELECT DISTINCT AGE, 
       FIRST_VALUE(LEVEL) OVER (PARTITION BY AGE ORDER BY COUNT(*) DESC) LEVEL 
FROM STUDENT  
GROUP BY AGE, LEVEL

или, если вы хотите, чтобы связи были возвращены, используйте RANK() функцию window:

 SELECT t.*
FROM (
  SELECT AGE, LEVEL,
         RANK() OVER (PARTITION BY AGE ORDER BY COUNT(*) DESC) rnk 
  FROM STUDENT 
  GROUP BY AGE, LEVEL 
) t
WHERE t.rnk = 1

Эти запросы работают для MySQL 8.0

Метки: