Блог

У меня есть следующий вопрос. Этот запрос позволяет мне создать список детей и контактов членов их семей (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-й семейный контакт.

Вот как это должно выглядеть

Как бы я выполнил эту задачу?

Я не знаю, что вы подразумеваете под «первой записью», потому что таблицы 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;
 

Используйте функцию окна 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;
 

Вы можете сделать это с 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