В книге "Mastering Perl" есть глава "Философия борьбы с неразрешимыми проблемами от Брайена ди Фоя". Один из рецептов звучит так: "А пытались ли вы поговорить об этом с Мишкой?"
На одной из работ,-- рассказывает автор,-- у меня был коллега, к которому я всегда обращался за советом как только что-то не получалось. Большинство таких консультаций заканчивалось одинаково: уже после третьего предложения я понимал, в чем дело, и обрывал свой монолог. Теперь,-- продолжает Брайен де Фой -- у меня возле компьютера живет плюшевый Мишка, с которым я вслух обсуждаю рабочие проблемы, дабы не беспокоить коллег.
Я вспомнил этот совет когда ломал голову над тестовым заданием следующего содержания:
Есть две таблицы:У меня сложные отношения с SQL. Периодически в него погружаюсь и решаю довольно сложные задачи. Но стоит полгода не позаниматься -- все забывается чуть ли не начисто. (Нечто подобное, но еще в большей степени происходит с XSLT). Ясно было, что надо использовать GROUP и MAX, но как объединить все это в один запрос?...
table1 (table1_id integer, name varchar(255)),
table2 (table2_id integer, table1_id integer, alias varchar(255))
Выбрать записи из table1, для которых количество подчинённых записей в table2 максимально.
Я прокрался в спальню, к детской кроватке. У пятилетнего Степы есть несколько медведей, в том числе два белых брата Кнут и Олаф Хаммундсены. Интуиция подсказывала, что для моих целей подойдет Винни-Пух. Через пять минут я четко и с расстановкой объяснял ему, в чем проблема. А через 10 минут решение было готово. Вот оно:
SELECT table1_id FROM table2
GROUP BY table1_id
HAVING COUNT(*) =
(
SELECT MAX(table2_count)
FROM (
SELECT COUNT(*) AS table2_count
FROM table2
GROUP BY table1_id
) AS inner_table
);
GROUP BY table1_id
HAVING COUNT(*) =
(
SELECT MAX(table2_count)
FROM (
SELECT COUNT(*) AS table2_count
FROM table2
GROUP BY table1_id
) AS inner_table
);
- Узнать, сколько дочерних записей приходится на каждую родительскую запись.
- Найти максимум.
- Выбрать строки, где количество дочерних записей равно найденному максимуму.
7 комментариев:
А если попробовать так:
SELECT
table1.table1_id,
sum(1) AS qty
FROM table1 LEFT JOIN table2 ON
table1.table1_id = table2.table1_id
GROUP BY table1.table1_id
ORDER BY qty DESC
LIMIT 1;
На sqlite3 проверил, работает.
Только возвращает лишний столбец qty - кол-во подчиненных записей.
Если по порядку, то пусть у нас для примера будут такие данные:
table1_id|name
1|alpha
2|betta
3|gamma
4|delta
table2_id|table1_id|alias
1|1|a1
2|1|a2
3|2|b1
4|3|g1
5|3|g2
6|3|g3
7|3|g4
8|4|d1
Тогда запрос
SELECT * FROM table1
LEFT JOIN table2
ON table1.table1_id = table2.table1_id;
вернет:
table1_id|name|table2_id|table1_id|alias
1|alpha|1|1|a1
1|alpha|2|1|a2
2|betta|3|2|b1
3|gamma|4|3|g1
3|gamma|5|3|g2
3|gamma|6|3|g3
3|gamma|7|3|g4
4|delta|8|4|d1
Далее:
SELECT
table1.table1_id,
sum(1) AS qty
FROM table1 LEFT JOIN table2
ON table1.table1_id = table2.table1_id
GROUP BY table1.table1_id;
Получаем ID первой таблицы и количество подчиненных записей во второй.
table1.table1_id|qty
1|2
2|1
3|4
4|1
Потом добавляем
...ORDER BY qty DESC, и запись
3|4
(table1_id=3, кол-во подчиненных = 4)
оказывается первой в запросе.
И наконец, LIMIT 1,
потому что остальные записи нам тогда не нужны.
Спасибо, красивое решение и, наверняка, более эффективное. Я не догадался использовать SUM.
select orders.*, count(*) as cnt
from orders left join orders_detail on orders_detail.order_id = orders.id
group by orders.id
order by cnt desc limit 1;
...ээээ, а где тут должно быть sum()??
проверено в mysql5
Отправить комментарий