Занимательная задачка по SQL

[dil]

Как обычно, нарисовалась из практики. Рассмотрим простенькую базу данных типа “классный журнал”. Не в смысле “очень хороший”, а в который школьникам оценки ставят.

Три таблицы. Ученики: students(stud_id, stud_name), предметы: subjects(subj_id, subj_name) и оценки, полученные учениками: scores(stud_id, subj_id, date, score). В реальности тут ещё должна быть таблица классов, привязка учеников к этим классам типа, привязка предметов к классам, но для простоты примера я эти усложнения убрал. Будем считать, что эта база для одного класса, и каждый ученик может получить не более одной оценки в день по данному  предмету.

Задача: для конкретного ученика (по его stud_id) и конкретной даты вывести _полный_ список предметов и оценки, полученные этим учеником за эту дату. То есть, если оценка по данному предмету в этот день есть — вывести её, если нет — вывести прочерк, NULL или ещё что-нибудь, обозначающее отсутствие оценки.

Ограничение: это надо сделать одним запросом. Можно, конечно, сначала взять полный список предметов и для каждого послать отдельный запрос, но это крайне неэффективно. База для конкретности пусть будет MySQL.

На первый взгляд такие задачи решаются с помощью subjects LEFT OUTER JOIN scores. Но… он нормально работает только когда таблицы объединяются по уникальному полю, а в данном случае в таблице scores поле subj_id не уникально. Соответственно, если там есть хоть одна запись для данного предмета (длялюбого ученика за любую дату), то в результате JOIN’а не окажется строк, где score, stud_id и date есть NULL. А если потом к этому результату применить ещё WHERE stud_id=… AND date=…, то даже те строки, где NULLы могли случайно оказаться, будут удалены, поскольку не соответствуют условию.

Вот и как это сделать? Подсказка: это возможно. И на самом деле, оказалось очень просто.

Оригинал этой записи в личном блоге.
Любые материалы из этого блога запрещается использовать на сайте livejournal.ru в любой форме и любом объёме.

Comments

[lazyboa.ya.ru]

select subj_id,subj_name from subjects as s left join (select subj_id,score from scores where stud_id=? and sc_date=?) as sc on s.subj_id=sc.subj_id

[dil]

Ага, так тоже работает. Правда, тут вместо score выводится subj_id, но поменять несложно.

Мне почему-то не удалось сходу воткнуть в JOIN вместо таблицы результат SELECT'а, поэтому я применил несколько другой синтаксис для примерно того же самого :)

[lazyboa.ya.ru]

join таблицы на саму себя? Это имело смысл в 3.23, пока не реализовали subqueries.

[dil]

Как саму на себя? Это вданном случае не имеет смысла.
JOIN тех же двух таблиц, но без явно указанного подзапроса

> но без явно указанного подзапроса

[poige.livejournal.com]

Можешь сделать explain и сравнить оба варианта. :)

Re: > но без явно указанного подзапроса

[dil]

explain в mysql только приблизительно показывает, какие индексы он может использовать, и оценивает количество проверяемых строк. А вопрос был не про эффективность, а про то, как это в принципе сделать.

Re: > но без явно указанного подзапроса

[poige.livejournal.com]

Тут, IMO, суть в том, что с точки зрения SQL-движка это никак не будет отличаться — лишь форма записи разная.

[kranov.livejournal.com]

не хватает одной таблицы: calendar

students join calendar LEFT OUTER JOIN scores LEFT OUTER JOIN subjects

[dil]

Не понял. Чем поможет calendar, если я из scores уже по конкретной дате выбираю?

[kranov.livejournal.com]

тем что scores надо с чем-то перемножать чтобы выбрать пустые строки.

пропущенные сущности обычная ошибка проектирования, из-за которой приходится перемножать с самими собой (строя пропущенные сущности на ходу)

[kranov.livejournal.com]

ну и на конкретную дату можно перемножить с чем угодно возвращающим одну строку.

[dil]

Календарь помог бы выбрать даты, за которые нет ни одной записи в scores, а тут надо subject'ы, для которых нет записей.

[starcat13]

Вот так?

select subj_name, score from scores a left join subjects b on a.subj_id=b.subj_id and a.stud_id= and a.date=

[dil]

Примерно так, только subjects left outer join scores. Если scores будут слева, то не получится.

[starcat13]

сорри, точно.