#sql #postgresql
Вопрос:
У меня есть следующий вопрос. Этот запрос позволяет мне создать список детей и контактов членов их семей (contactpupilID).
select s.studentnr,pc.pupilid, pc.contactpupilid, p2.mainmail
from student s
join pupil p on p.id = s.pupilid
join pupilcontact pc on pc.pupilid = p.id
join pupil p2 on p2.id = pc.contactpupilid
where CURRENT_DATE between pc.validfrom and pc.validuntil
order by pc.pupilid
У каждого ребенка может быть от 0 до 3 семейных контактов (0, потому что при регистрации контакт не был добавлен).
У каждого семейного контакта (contactpupilid) есть поле электронной почты. Однако бывают случаи, когда у всех семейных контактов есть электронное письмо или 1 из них, или ни у одного из них.
В моем списке нужно выбрать ребенка с семейным контактом(contactpupilid), у которого есть электронная почта. Выбранным контактом семьи должен быть тот, у которого есть электронное письмо.
Если ни у одного из семейных контактов нет электронной почты, то по умолчанию следует выбрать 1-й семейный контакт.
Вот как это должно выглядеть
Как бы я выполнил эту задачу?
Ответ №1:
Я не знаю, что вы подразумеваете под «первой записью», потому что таблицы SQL не упорядочены. Я могу предположить, что вы имеете в виду тот, у которого меньше contactpupilid
всего .
То, что вы описали, — это то, что distinct on
делает:
select distinct on (s.studentnr) s.studentnr, pc.pupilid, pc.contactpupilid, p2.mainmail
from student s join
pupil p
on p.id = s.pupilid join
pupilcontact pc
on pc.pupilid = p.id join
pupil p2
on p2.id = pc.contactpupilid
where CURRENT_DATE between pc.validfrom and pc.validuntil
order by s.studentnr, (p2.mainmail is not null) desc;
Комментарии:
1. 1 запись не первая. Я хочу выбрать 1 запись!
2. можете ли вы поверить, что адрес электронной почты никогда не был пустым? каким-то образом в нем были какие-то пустые космические символы. поэтому я заказал его по ссылке char_length(p2.mainmail). Спасибо
Ответ №2:
Используйте функцию окна ROW_NUMBER() в своем запросе, чтобы сначала ранжировать строки, содержащие электронное письмо:
with cte as (
select s.studentnr, pc.pupilid, pc.contactpupilid, p2.mainmail,
row_number() over (partition by s.studentnr order by p2.mainmail is not null desc, pc.contactpupilid) rn
from student s
join pupil p on p.id = s.pupilid
join pupilcontact pc on pc.pupilid = p.id
join pupil p2 on p2.id = pc.contactpupilid
where CURRENT_DATE between pc.validfrom and pc.validuntil
)
select studentnr, pupilid, contactpupilid, mainmail
from cte
where rn = 1
order by pupilid;
Ответ №3:
Вы можете сделать это с CTE вот так
with temp as (
select s.studentnr,pc.pupilid, pc.contactpupilid, p2.mainmail,row_number() over (partition by pupilid order by pupilid) as row_number
from student s
join pupil p on p.id = s.pupilid
join pupilcontact pc on pc.pupilid = p.id
join pupil p2 on p2.id = pc.contactpupilid
where CURRENT_DATE between pc.validfrom and pc.validuntil
order by pc.pupilid
)
select *
from temp
where row_number = 1
Комментарии:
1. он создает столбец с именем rownumber, и его значение всегда равно 1, поэтому случаи, когда он не выбирал, были 2-м семейным контактом, у которого есть электронное письмо, не выбирается.
2. Я не хочу выбирать 1-ю запись, мне нужна только 1 запись, у которой есть адрес электронной почты. Если ни у кого нет адреса электронной почты, то по умолчанию выбирается 1-й!