Занимательная задачка по SQL
Как обычно, нарисовалась из практики. Рассмотрим простенькую базу данных типа “классный журнал”. Не в смысле “очень хороший”, а в который школьникам оценки ставят.
Три таблицы. Ученики: 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 в любой форме и любом объёме.
(
| Комментировать в Dreamwidth)