我需要展示“预定与实际”报告,该报告展示了持有学校出勤数据的MySQL数据库中两个表之间的差异。
我有两个称为Booking和Attendance的表。
表中存储的数据如下:
Booking表:
出席记录。
基本上,我想将输出显示为:
任何帮助都将不胜感激。
我有两个称为Booking和Attendance的表。
表中存储的数据如下:
Booking表:
Id Student Date IsAbsent
1 John 20160216 1 //NO
2 Bob 20160217 1 //NO
3 Zara 20160218 1 //NO
出席记录。
Id Student Date IsAbsent
1 John 20160216 0 //YES
2 Bob 20160217 0 //YES
3 Mary 20160217 1 //NO
基本上,我想将输出显示为:
。
**Id | Student | Day_1 | Day_2 | Day_3**
==== ========= ====== ======= ======
1 | John |ABSENT | NULL | NULL
2 | Bob |NULL |ABSENT | NULL
3 | Mary |NULL |NEW | NULL
4 | Zara |DELETED|NULL | NULL
缺席 在预订表中,约翰被标记为1(假),但在出勤表中,他被标记为0(是),因此我想显示为“缺席”。
新增 玛丽仅在出勤表中有记录,但在预订表中没有记录。
已删除 萨拉最初被预订,并在预订表中,但不在出勤表中。
我已经创建了 SQL Fiddle 的模式和查询,但它总是返回空值。
我的 SQL 查询如下所示...
SELECT * FROM
((SELECT
a.Student as student,
MAX( case
when a.DropDate='20160216' && a.IsAbsent=0 && s.IsAbsent=1 then 'Absent'
when (select count(*) from attendance where DropDate='20160216')=0 && (select count(*) from staging where DropDate='20160216')>0 then 'DELETED Booking'
when (select count(*) from attendance where DropDate='20160216')>0 && (select count(*) from staging where DropDate='20160216')=0 then 'New Booking'
else ' ' end ) as 'day_1',
MAX( case
when a.DropDate='20160217' && a.IsAbsent=0 && s.IsAbsent=1 then 'Absent'
when (select count(*) from attendance where DropDate='20160217')=0 && (select count(*) from staging where DropDate='20160217')>0 then 'DELETED Booking'
when (select count(*) from attendance where DropDate='20160217')>0 && (select count(*) from staging where DropDate='20160217')=0 then 'New Booking'
else ' ' end ) as 'day_2',
MAX( case
when a.DropDate='20160218' && a.IsAbsent=0 && s.IsAbsent=1 then 'Absent'
when (select count(*) from attendance where DropDate='20160218')=0 && (select count(*) from staging where DropDate='20160218')>0 then 'DELETED Booking'
when (select count(*) from attendance where DropDate='20160218')>0 && (select count(*) from staging where DropDate='20160218')=0 then 'DELETED Booking'
else ' ' end ) as 'day_3'
FROM Attendance a LEFT JOIN Booking s on a.Student=s.Student
WHERE a.DropDate IN ('20160216','20160217','20160218')
AND NOT EXISTS
( SELECT 1
FROM Booking AS p
WHERE p.Student = a.Student
AND p.IsAbsent = a.IsAbsent
AND p.DropDate = a.DropDate
)
)
UNION
(SELECT
t.Student as student,
MAX( case
when t.DropDate='20160216' && a.IsAbsent=0 && t.IsAbsent=1 then 'Absent'
when (select count(*) from attendance where DropDate='20160216')=0 && (select count(*) from staging where DropDate='20160216')>0 then 'DELETED Booking'
when (select count(*) from attendance where DropDate='20160216')>0 && (select count(*) from staging where DropDate='20160216')=0 then 'New Booking'
else ' ' end ) as 'day_1',
MAX( case
when a.DropDate='20160217' && a.IsAbsent=0 && t.IsAbsent=1 then 'Absent'
when (select count(*) from attendance where DropDate='20160217')=0 && (select count(*) from staging where DropDate='20160217')>0 then 'DELETED Booking'
when (select count(*) from attendance where DropDate='20160217')>0 && (select count(*) from staging where DropDate='20160217')=0 then 'New Booking'
else ' ' end ) as 'day_2',
MAX( case
when a.DropDate='20160218' && a.IsAbsent=0 && t.IsAbsent=1 then 'Absent'
when (select count(*) from attendance where DropDate='20160218')=0 && (select count(*) from staging where DropDate='20160218')>0 then 'DELETED Booking'
when (select count(*) from attendance where DropDate='20160218')>0 && (select count(*) from staging where DropDate='20160218')=0 then 'DELETED Booking'
else ' ' end ) as 'day_3'
FROM Booking t LEFT JOIN attendance a on t.Student=a.Student
WHERE t.DropDate IN ('20160216','20160217','20160218')
AND NOT EXISTS
( SELECT 1
FROM Attendance AS u
WHERE u.Student = t.Student
AND u.IsAbsent = t.IsAbsent
AND u.DropDate = t.DropDate
)
)) tbl
ORDER BY student
任何帮助都将不胜感激。