php获取两个文件不同记录,php – MySQL获得两个表的不同记录

我需要显示“Scheduled vs Actual”报告,该报告显示

MySQL数据库中两个表之间的差异,这两个表保持学校的出勤率.

我有两张名为Booking and Attendance的表.

存储在表格中的数据如下：

预订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(是)所以我想显示为’缺席’

新Mary只在考勤表中输入,但不在预订表中.

删除Zara最初预订并在预订表中,但不在出勤表中.

我创建了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

任何帮助将受到高度赞赏.

最佳答案 我不确定你可以在MySQL查询中输出内联布局,除非你做了一个程序……

我不知道这对你是否合适,因为它有一个略有不同的布局,但它的工作原理：(select s.Student,

s.ClassRoom,

s.DropDate,

if(s.IsAbsent=1&&a.IsAbsent=0,'ABSENT','PRESENT')

from Staging s inner join Attendance a on a.Student = s.Student and a.ClassRoom = s.ClassRoom and a.DropDate = s.DropDate)

UNION ALL

(Select s.Student,

s.ClassRoom,

s.DropDate,

'DELETED'

from Staging s left join Attendance a on a.Student = s.Student and a.ClassRoom = s.ClassRoom and a.DropDate = s.DropDate

where a.Student is null)

UNION ALL

(Select a.Student,

a.ClassRoom,

a.DropDate,

'NEW'

from Staging s right join Attendance a on a.Student = s.Student and a.ClassRoom = s.ClassRoom and a.DropDate = s.DropDate

where s.Student is null)

order by Student, DropDate;