php – MySQL获得两个表的不同记录

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了php – MySQL获得两个表的不同记录，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4776字，纯文字阅读大概需要7分钟。

内容图文

我需要显示“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;

内容总结

以上是互联网集市为您收集整理的php – MySQL获得两个表的不同记录全部内容，希望文章能够帮你解决php – MySQL获得两个表的不同记录所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端