校内学生查询

Question

假设我有一个schools表和一个students表。students表连接到schools表，并具有NAME和BIRTHDATE列。

我需要所有学校里有学生，他们被称为“吉姆”或“迈克”，并出生在同一天。

类似于：

select *
  from schools s
  join student st in st.schoolId = s.Id
  where (...)

Answer 1

可能使用两个时间表相关的内joi按出生日期

select t1.id from ( 
    select s.id , st.name, st.birthdate
    from schools s
    join student st in st.schoolId = s.Id
    where st.name  = 'jim' 
) t1 
inner join ( 
    select s.id , st.name, st.birthdate
    from schools s
    join student st in st.schoolId = s.Id
    where st.name  = 'mike' 
) t2 on t1.birthdate = t2.birthdate

Answer 2

你可以通过两次把学生和学校联系起来来做到这一点。

select  distinct s.*
from    schools s
join    student st1
on      st.schoolId = s.Id
join    student st2
on      st.schoolId = s.Id
where   st1.birthdate = st2.birthdate and
        st1.name = 'Jim' and
        st2.name = 'Mike'

Answer 3

您可以简单地使用条件聚合来查找同时具有每个birthdate和join (或使用IN)的名称的join和IN表，以获取相关的详细信息。

select s.*
from schools s
join (
    select distinct schoodId
    from student
    where name in ('jim', 'mike')
    group by schoodId,
        birthdate
    having count(distinct name) = 2
    ) st on s.Id = st.schoodId;