Показать множественный выбор с 3 столбцами в MYSQL и значениями NULL

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

зрачки:

id_pupil
name
surname
email
user
pass
level
class

инциденты:

id_incidents
date
time
type_incident(miss, delay or attitude)
comments
id_pupil
id_user
subject
id_trimester

И я хотел бы получить это:

Я пишу это:

select pupils.id_pupil, name, surname, count(type_incident) as misses 
from pupils left join incidents using (id_pupil) 
where type_incident='miss' and level=1 and class='B' and id_trimester=1 
group by id_pupil

и я получаю столбец с промахами каждого ученика. Тем не менее, я не получаю нулевые значения, потому что я хочу также получить учеников с 0 промахами.

То же самое с задержками и отношением:

select pupils.id_pupil, name, surname, count(type_incident) as misses 
from pupils left join incidents using (id_pupil) 
where type_incident='delay' and level=1 and class='B' and id_trimester=1 
group by id_pupil

select pupils.id_pupil, name, surname, count(type_incident) as misses 
from pupils left join incidents using (id_pupil) 
where type_incident='attitude' and level=1 and class='B' and id_trimester=1 
group by id_pupil

И я хочу получить все в одной таблице, как описано на картинке.

Спасибо!

Источник

3 ответа

Решение

Поставить вам условия в sum(), Суммирует, сколько раз ваше состояние было верным (1)

select pupils.id_pupil, name, surname, 
       sum(type_incident='miss' and level=1 and class='B' and id_trimester=1) as misses,
       sum(type_incident='delay' and level=1 and class='B' and id_trimester=1) as delays,
       sum(type_incident='attitude' and level=1 and class='B' and id_trimester=1) as attitudes
from pupils 
left join incidents using (id_pupil) 
group by id_pupil, name, surname

SQLFiddle demo

Источник

Я думаю, что ответ Юргена был бы лучше, чем мой, так как код более читабелен, но так как я уже печатал свой, я продолжу и опубликую другой способ достижения того же результата: два моих исходных предложения были добавить name а также surname к вашему заявлению GROUP BY; Кроме того, переместить условие and id_trimester=1 от ГДЕ до вашего ЛЕВОГО СОЕДИНЕНИЯ, так что-то вроде этого:

select p.id_pupil, name, surname, count(i.type_incident) as misses, count(i2.type_incident) as delays, count(i3.type_incident) as attitudes
from pupils p
left join incidents i on i.id_pupil = p.id and i.type_incident='miss' and i.id_trimester=1
left join incidents i2 on i2.id_pupil = p.id and i2.type_incident='delay' and i2.id_trimester=1
left join incidents i3 on i3.id_pupil = p.id and i3.type_incident='attitude' and i3.id_trimester=1
where p.level=1 and p.class='B'
group by p.id_pupil, name, surname
Источник 
SELECT CONCAT(p.name, ' ', p.surname) AS Pupil, t1.misses, t2.delays, t3.attitude
FROM pupils AS p
LEFT JOIN (SELECT COUNT(*) AS misses FROM id_incidents WHERE type_incident = 'miss' and level=1 and class='B' and id_trimester=1) AS t1 ON (t1.id_user = p.id_pupil)
LEFT JOIN (SELECT COUNT(*) AS delays FROM id_incidents WHERE type_incident = 'delay' and level=1 and class='B' and id_trimester=1) AS t2 ON (t2.id_user = p.id_pupil)
LEFT JOIN (SELECT COUNT(*) AS attitude FROM id_incidents WHERE type_incident = 'attitude' and level=1 and class='B' and id_trimester=1) AS t3 ON (t3.id_user = p.id_pupil)
Источник
Другие вопросы по тегам