mysql视图包含运行状态(mysql view to contain running status)

 我正在为学校设计一个基于网络的时钟，用于登录学生和员工。 
 
 我有两张桌子： 
 
student_time_clock: student_id (foreign key to student), time_stamp, by_adult_id (foreign key to adult)

staff_time_clock: staff_id (foreign key to staff), time_stamp
 
 我给出的一个要求是观察者能够在一个视图中查看记录，如下所示： 
 
+----------+------------+-------------------------+---------------------+
| In / Out | Time Stamp | Person Logged in or out | Logged in or out by |
+----------+------------+-------------------------+---------------------+
 
 除了带有联合的第一列，我可以做所有事情。 我无法弄清楚如何获得第一列。 这是我用于联合的查询： 
 
SELECT stc.entry                             AS "Time Stamp", 
       Concat(s.lastname, ",", s.firstname) AS "Punched", 
       Concat(a.lastname, ",", a.firstname) AS "By" 
FROM   student_time_clock stc, 
       student s, 
       adult a 
WHERE  stc.student_id = s.id 
       AND stc.by_adult_id = a.id 
UNION 
SELECT atc.entry                             AS "at", 
       Concat(a.lastname, ",", a.firstname) AS "Staff", 
       Concat(a.lastname, ",", a.firstname) AS "By" 
FROM   staff_time_clock atc, 
       staff s, 
       adult a 
WHERE  atc.staff_id = s.id 
ORDER  BY "time stamp" DESC; 
 
 我尝试过使用CASE，例如： 
 
CASE COUNT( entry ) % 2 WHEN 1 THEN "In" WHEN 0 THEN "Out"
 
 当那种情况出现时，我只在结果中得到一行。 
 
 有什么建议么？ 前端是PHP，但我想将其创建为数据库中的视图 

I'm designing a web based time clock for a school for logging in both students and staff. 
 
I have two tables:
 
student_time_clock: student_id (foreign key to student), time_stamp, by_adult_id (foreign key to adult)

staff_time_clock: staff_id (foreign key to staff), time_stamp
 
One of the requirements I've been given is that an observer be able to see the records in one view like the following:
 
+----------+------------+-------------------------+---------------------+
| In / Out | Time Stamp | Person Logged in or out | Logged in or out by |
+----------+------------+-------------------------+---------------------+
 
I can do all but the first column with a union. I can't figure out how to get that first column though. Here's the query I'm using for the union:
 
SELECT stc.entry                             AS "Time Stamp", 
       Concat(s.lastname, ",", s.firstname) AS "Punched", 
       Concat(a.lastname, ",", a.firstname) AS "By" 
FROM   student_time_clock stc, 
       student s, 
       adult a 
WHERE  stc.student_id = s.id 
       AND stc.by_adult_id = a.id 
UNION 
SELECT atc.entry                             AS "at", 
       Concat(a.lastname, ",", a.firstname) AS "Staff", 
       Concat(a.lastname, ",", a.firstname) AS "By" 
FROM   staff_time_clock atc, 
       staff s, 
       adult a 
WHERE  atc.staff_id = s.id 
ORDER  BY "time stamp" DESC; 
 
I've tried using a CASE such as:
 
CASE COUNT( entry ) % 2 WHEN 1 THEN "In" WHEN 0 THEN "Out"
 
When that case is there though I only get a single row in the result.
 
Any suggestions? The front end is in PHP but I'd like to create this as a view in the database

原文：https://stackoverflow.com/questions/16738648