Часто возникает задача выбрать из базы все записи, у которых определенное поле имеет экстремальное значение в пределах каждой группы.
Например, есть такая таблица:
mysql> SELECT * from fruits;
+--------+------------+-------+
| type | variety | price |
+--------+------------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+------------+-------+
9 rows in set (0.00 sec)
Задача: получить информацию по самым дешевым сортам фруктов каждого типа. То есть, в итоге должно получится:
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
+--------+----------+-------+
Я раньше всегда решал подобные задачи с помощью JOIN и подзапроса:
SELECT f1.* FROM fruits AS f1
INNER JOIN (
SELECT type, MIN(price) AS price
FROM fruits
GROUP BY type
) AS f2 ON f2.type = f1.type AND f2.price = f1.price
GROUP BY type
GROUP BY type нужен, если необходимо, чтобы на каждый тип фрукта было только по одному сорту, даже если цены на некоторые сорта совпадут.
Но сегодня мне потребовалось сделать нечто вроде: выбери все самые дешевые разновидности каждого типа из тех, в которых встречается буква "a". Понятно, что запрос
SELECT f1.* FROM fruits AS f1
INNER JOIN (
SELECT type, MIN(price) AS price
FROM fruits
WHERE variety LIKE '%a%'
GROUP BY type
) AS f2 ON f2.type = f1.type AND f2.price = f1.price
GROUP BY type
будет работать правильно лишь до тех пор, пока не объявится несколько сортов одного фрукта по одной цене, но в названии одного из них будет буква "a", а в другом нет. В этом случае условие придется продублировать:
SELECT f1.* FROM fruits AS f1
INNER JOIN (
SELECT type, MIN(price) AS price
FROM fruits
WHERE variety LIKE '%a%'
GROUP BY type
) AS f2 ON f2.type = f1.type AND f2.price = f1.price
WHERE variety LIKE '%a%'
GROUP BY type
Теперь представьте, что условие не одно, а несколько, к тому же они зависят от пользовательского ввода и весь SQL должен генерироваться автоматически.
На этом месте я сказал себе: "Стоп, надо искать другой выход".
Первый поверхностный поиск дал интересный, но уродский способ использовать GROUP_CONCAT в качестве FIRST, который к тому же в нашем случае вообще ничего не даст, а упомянул я о нем исключительно ради увеселения.
Затем наткнулся на переписку участника форума с дёрганым модератором. В конце концов участник форума сам нашел следующее решение (у него, конечно, не про фрукты):
SELECT f1.* FROM fruits AS f1
LEFT JOIN fruits AS f2 ON f2.type = f1.type AND f1.price > f2.price
WHERE f2.type IS NULL
Очень интересное решение, но такой JOIN устрашает при большом количестве строк — а это как раз мой случай. Но все равно, уже теплее.
В третий раз закинул он невод…
И первый же результат вывел меня на эту статью: How to select the first/least/max row per group in SQL. Я выбрал предпоследнее решение, без магии с FORCE INDEX. Оно выглядит так:
SET @type = NULL;
SELECT type, variety, price FROM (
SELECT (@num := IF(@type = type, @num+1, 1)) AS num, @type := type AS type, variety, price
FROM fruits
WHERE variety LIKE '%a%'
ORDER BY type, price
) AS t
WHERE num = 1;
Глобальные переменные в MySQL — сила, пусть даже это всего лишь уродский костыль.
