Подзапрос SQL
У меня есть данные SQL, которые выглядят так
events
id name capacity
1 Cooking 10
2 Swimming 20
3 Archery 15
registrants
id name
1 Jimmy
2 Billy
3 Sally
registrant_event
registrant_id event_id
1 3
2 3
3 2
Я хотел бы выбрать все поля в «событиях», а также дополнительное поле, в котором указано количество людей, которые в настоящее время зарегистрированы для этого события. В этом случае у Archery будет 2 регистратора, у плавания - 1, а у кулинарии - 0.
Я предполагаю, что это можно сделать с помощью одного запроса, но я не уверен в правильном синтаксисе. Как можно было бы написать запрос для получения этих данных?
Обновление: Спасибо за отличные ответы, вы все молодцы!
Ответы 7
SELECT e.*, ISNULL(ec.TotalRegistrants, 0) FROM events e LEFT OUTER JOIN
(
SELECT event_id, Count(registrant_id) AS TotalRegistrants
FROM registrant_event
GROUP BY event_id
) ec ON e.id = ec.event_id
Это используется в среде mysql, поэтому я изменил ISNULL () на IFNULL (), и он отлично работал. Спасибо! dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
SELECT Events.ID, Events.Name, Events.Capacity,
ISNULL(COUNT(Registrant_Event.Registrant_ID), 0)
FROM Events
LEFT OUTER JOIN Registrant_Event ON Events.ID = Registrant_Event.Event_ID
GROUP BY Events.ID, Events.Name, Events.Capacity
GROUP BY Events.ID, Events.Name, Events.Capacity неверен
В этом случае правильным способом использования GROUP BY является подзапрос. Помещение всех выбранных полей в GROUP BY - распространенная ошибка.
1) есть разница между «не правильно» и «не сработает» и «не в хорошем стиле». 2) поскольку в вопросе не упоминается никакая база данных, мы не знаем, поддерживает ли эта база данных подзапросы.
подзапрос - один из тегов статьи.
Это также название вопроса
Подзапрос также является неподходящим решением. Это тот случай, когда люди бросились отвечать на вопрос, не задумываясь о необходимости.
Учитывая, что вопрос конкретно касается подзапроса, я не думаю, что это возможно.
select e.id, e.name, e.capacity, IsNull(re.eventCount,0) from events e
left join (
select count(event_id) as eventCount, event_id from registrant_event group by event_id
) re
on e.id = re.event_idЯ думаю, вы имеете в виду количество (registrant_id)
Никогда не думал, что так будет, но вижу, что получится. Какие преимущества?
Честно говоря, я не знаю, есть ли в этом какие-то преимущества, но я в этом сомневаюсь. Я просто так и сделал, потому что это то, по чему мы группируемся.
SELECT
events.*
, COUNT(registrant_event.registrant_id) AS registrantsCount
FROM events
LEFT JOIN registrant_event ON events.id = registrant_event.event_id
GROUP BY events.id
это не сработает, если в таблице событий есть поля, не входящие в группу по предложению. IE: все, кроме id
Этот запрос хрипит на ANSI-совместимых серверах SQL ... включая MySQL в режиме ANSI (запускается с помощью переключателя --ansi).
SELECT e.id, count(*) AS NumRegistrants
FROM events e
LEFT JOIN registrant_event re ON re.event_id=e.id
GROUP BY e.id
Обратите внимание, что это вернет 1 вместо 0 для событий без регистрации. Чтобы вместо этого он отображал 0, вам нужно немного усложнить:
SELECT *,
(SELECT COUNT(*) FROM registrant_event WHERE event_id=id) AS NumRegistrants
FROM events
использование счетчика в столбце / поле, содержащем нули, не будет их считать ... например, COUNT (re.registrant_id)
Верно. COUNT (*) является источником многих ошибок в этих случаях.
Я тестировал это в Oracle 11g, и, похоже, он работает
SELECT e.id, e.name, e.capacity, COUNT(re.event_id)
FROM events e
LEFT JOIN registrant_event re
ON e.id = re.event_id
GROUP BY e.id, e.name, e.capacity
select d.id1, d.name, d.cappacity, count(d.b_id) as number_of_people
from (select eve.id1,eve.name,eve.cappacity,re_eve.b_id
from eve left join re_eve on eve.id1 = re_eve.b_id) d
group by d.id1, d.name, d.cappacity
Я предполагаю, что вы имеете в виду e.id вместо e.event_id в последней строке.