mysql - 当另一列中仅存在该值的多个数据时，如何从列中提取数据？

Question

SQL 代码:

select student.sex, class.date 
from ((student INNER JOIN student_course ON student.name=student_course.Student) 
    INNER JOIN class ON student_course.Course=class.Course);

结果:

sex  date   
m   25.2.19 
m   27.2.19 
m   27.2.19 
m   27.2.19 
m   25.2.19 
m   27.2.19 
m   25.2.19     
f   25.2.19 
f   27.2.19 
f   27.2.19 
f   25.2.19 
f   26.2.19 

现在我的目标是获取男女学生出现的日期。所以我将代码修改为:

SELECT o.date 
FROM (  select student.sex, class.date 
        from ((student INNER JOIN student_course ON student.name=student_course.Student) 
        INNER JOIN class ON student_course.Course=class.Course)) AS o 
WHERE o.sex='m' AND o.sex='f';

结果为 NULL。但我想要的结果是:

date 
25.2.19
27.2.19

Answer 1

您可以使用聚合进行JOIN，无需任何子查询:

select c.date
from student s inner join
     student_course sc
     on s.name = sc.Student inner join
     class c
     on sc.Course = c.Course
where s.sex in ('F', 'M')
group by c.date;

这将显示找到male或female的dateS，但是您的示例输出表明:

select c.date
from student s inner join
     student_course sc
     on s.name = sc.Student inner join
     class c
     on sc.Course = c.Course
where s.sex in ('F', 'M')
group by c.date
having count(distinct s.sex) = 2;