MySQL获取范围内每个日期的所有学生的出勤率

Question

>我有以下表格
学生表

id  name
1    A1
2    A2
3    A3

和阿腾丹斯表

student_id   status  date
1              P      2020-02-14
2              P      2020-02-14
1              A      202-02-15

我想要的是，无论数据表中是否存在数据，都应显示所有日期的出勤情况。我尝试了以下查询

SELECT student.name, attendance.status 
from student
LEFT JOIN attendance ON student.id = attendance.student_id
AND attendance.date = '2020-02-14'

通过上面的查询，我得到的输出为

id  name   status     date
1    A1      P      2020-02-14
2    A2      P      2020-02-14
3    A3      null   null

但我希望输出是

id  name   status     date
1    A1      P      2020-02-14
2    A2      P      2020-02-14
3    A3      null   2020-02-14
1    A1      A      2020-02-15
2    A2      null   2020-02-15
3    A3      null   2020-02-15

是否可以使用单个查询实现上述输出？还是需要通过将查询输出转换为 JSON 来执行一些操作才能获得所需的输出？

Answer 1

假设没有所有学生都没有出勤记录的日期，您可以使用SELECT DISTINCT查询从出勤表中找到所有相关日期;然后您可以将其CROSS JOIN到students表中并LEFT JOIN出勤以获得所需的结果：

SELECT s.id, s.name, a.status, d.date
FROM (SELECT DISTINCT `date` FROM attendance) d
CROSS JOIN students s
LEFT JOIN attendance a ON a.date = d.date AND a.student_id = s.id
ORDER BY d.date, s.id

输出：

id  name    status  date
1   A1      P       2020-02-14
2   A2      P       2020-02-14
3   A3      null    2020-02-14
1   A1      A       2020-02-15
2   A2      null    2020-02-15
3   A3      null    2020-02-15

在 dbfiddle 上演示

Answer 2

您可以使用递归 CTE 生成日期。 然后交叉连接和左连接：

with recursive dates as (
select date('2020-02-14') as dte
union all
select dte + interval 1 day
from dates
where dte < '2020-02-16'
)
select s.id, s.name, a.status, d.dte
from dates d cross join
students s left join
attendance a
on a.student_id = s.id and
a.date = d.dte;