我有以下表
学生表
id name
1 A1
2 A2
3 A3
和Atendance表
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来进行一些操作，以获得所需的输出吗？
假设没有所有学生都没有出勤记录的日期，您可以使用Select Distinction查询从考勤表中找到所有相关日期； 然后您可以交叉加入到students表，左加入到考勤，以获得您想要的结果:
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上的演示
您可以使用递归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;
