复杂的选择查询，用于简单的输入/输出记录

本文介绍了复杂的选择查询，用于简单的输入/输出记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在制作一个简单的超时和超时系统.我有3对进/出.

I am making a simple time in and time out system. I have 3 pairs of in/out.

emp_id td_id status timestamp remarks 35 1 in 2013-12-19 10:15:09 late 35 2 out 2013-12-19 12:00:23 example 35 3 in 2013-12-19 13:00:23 35 4 out 2013-12-19 16:01:47 35 5 in 2013-12-19 18:01:17 35 6 out 2013-12-19 22:01:07 35 7 in 2013-12-20 10:00:12

这是我的查询:

SELECT a1.emp_id, a1.status, a1.timestamp, a2.status, a2.timestamp, a3.status, a3.timestamp, a4.status, a4.timestamp, a5.status, a5.timestamp, a6.status, a6.timestamp FROM overallrec a1 LEFT JOIN overallrec a2 ON a2.emp_id = a1.emp_id AND a2.status = 'out' LEFT JOIN overallrec a3 ON a3.emp_id = a1.emp_id AND a3.status = 'in' AND a3.timestamp <> a1.timestamp LEFT JOIN overallrec a4 ON a4.emp_id = a1.emp_id AND a4.status = 'out' AND a4.timestamp <> a2.timestamp LEFT JOIN overallrec a5 ON a5.emp_id = a1.emp_id AND a5.status = 'in' AND a5.timestamp <> a3.timestamp LEFT JOIN overallrec a6 ON a6.emp_id = a1.emp_id AND a6.status = 'in' AND a6.timestamp <> a4.timestamp WHERE a1.status = 'in'

这是我的结果:

emp_id status timestamp status timestamp status timestamp status timestamp status timestamp status timestamp 35 in 2013-12-19 10:15:09 out 2013-12-19 12:00:23 in 2013-12-19 13:00:23 out 2013-12-19 16:01:47 in 2013-12-19 18:01:17 out 2013-12-19 22:01:07 35 in 2013-12-20 10:00:12 out 2013-12-19 12:00:23 in 2013-12-19 13:00:23 out 2013-12-19 16:01:47 in 2013-12-19 18:01:17 out 2013-12-19 22:01:07

请注意，第一个输入"时间戳记值行2是另一个日期，但是下一个状态时间戳记和即将从上一个日期重复.我希望它在仍然为空时显示null，而不要复制上一个日期的值.换句话说，它应该在换日时生成另一行.

Notice the first 'in' timestamp value row 2 is another date, but the next status timestamp and soon are repeating from the previous date. I want it to display null when still empty and not to copy the value on previous date. In other words, it should generate another row on day change.

其他注释:当我在每个输入处添加注释时，它将被串联在表结果中.我希望它像这样:

Additional Remarks: when I add remarks at every input, it will be concatenated in the table result. I wanted it to be like this:

emp_id status timestamp status timestamp status timestamp status timestamp status timestamp status timestamp remarks 35 in 2013-12-19 10:15:09 out 2013-12-19 12:00:23 in 2013-12-19 13:00:23 out 2013-12-19 16:01:47 in 2013-12-19 18:01:17 out 2013-12-19 22:01:07 "Late, Example" 35 in 2013-12-20 10:00:12 null null null null null null null null null null "Straight time"

我该如何处理查询?还是如果没有查询，还有什么?

What should i do on my query? Or if not with the query, what else?

推荐答案

答案已更改，我忘了在timestamp1之前订购，这里是最终版本，将照顾多名员工:) sqlFiddle

Changed answer, i forgot to order by timestamp1,here's the final version that will take care of multiple employees :) sqlFiddle

SELECT T1.emp_id,T1.status1 as status1,T1.timestamp1 as timestamp1, T1.status2 as status2,T1.timestamp2 as timestamp2, T2.status1 as status3,T2.timestamp1 as timestamp3, T2.status2 as status4,T2.timestamp2 as timestamp4, T3.status1 as status5,T3.timestamp1 as timestamp5, T3.status2 as status6,T3.timestamp2 as timestamp6 FROM (SELECT * FROM (SELECT IF(((@row+1)=4) OR (@prevEmpId<>a1.emp_id),@row:=1,@row:=@row+1) as row,a1.emp_id,a1.status as status1,a1.timestamp as timestamp1, 'out' as status2, @prevEmpId:=a1.emp_id, (SELECT min(timestamp) as timestamp2 FROM overallrec a2 WHERE a2.timestamp > a1.timestamp AND a2.emp_id = a1.emp_id AND a2.status = 'out') as timestamp2 FROM overallrec a1,(SELECT @row:=0,@prevEmpId:=0)r WHERE a1.status = 'in' ORDER BY a1.emp_id,timestamp1 )T100 WHERE row=1 )T1 LEFT JOIN (SELECT * FROM (SELECT IF(((@row+1)=4) OR (@prevEmpId<>a1.emp_id),@row:=1,@row:=@row+1) as row,a1.emp_id,a1.status as status1,a1.timestamp as timestamp1, 'out' as status2, @prevEmpId:=a1.emp_id, (SELECT min(timestamp) as timestamp2 FROM overallrec a2 WHERE a2.timestamp > a1.timestamp AND a2.emp_id = a1.emp_id AND a2.status = 'out') as timestamp2 FROM overallrec a1,(SELECT @row:=0,@prevEmpId:=0)r WHERE a1.status = 'in' ORDER BY a1.emp_id,timestamp1 )T200 WHERE row=2 )T2 ON T1.emp_id = T2.emp_id AND DATE_FORMAT(T2.timestamp1,'%Y-%m-%d') = DATE_FORMAT(T1.timestamp1,'%Y-%m-%d') LEFT JOIN (SELECT * FROM (SELECT IF(((@row+1)=4) OR (@prevEmpId<>a1.emp_id),@row:=1,@row:=@row+1) as row,a1.emp_id,a1.status as status1,a1.timestamp as timestamp1, 'out' as status2, @prevEmpId:=a1.emp_id, (SELECT min(timestamp) as timestamp2 FROM overallrec a2 WHERE a2.timestamp > a1.timestamp AND a2.emp_id = a1.emp_id AND a2.status = 'out') as timestamp2 FROM overallrec a1,(SELECT @row:=0,@prevEmpId:=0)r WHERE a1.status = 'in' ORDER BY a1.emp_id,timestamp1 )T300 WHERE row=3 )T3 ON T1.emp_id = T3.emp_id AND DATE_FORMAT(T3.timestamp1,'%Y-%m-%d') = DATE_FORMAT(T1.timestamp1,'%Y-%m-%d')

但是，如果员工在一天之内先查询然后第二天就退出查询，则此查询将不起作用，因为该员工正在利用同一天的检查以进行LEFT JOIN操作

However this query won't work if employee clocks in one day and then then clocks out the next day because it's making use of the same day check in order to LEFT JOIN

OP请求一个视图，但是mySQL中的View不允许使用变量，因此我尝试编写一个不同的查询(不使用变量)，例如这样的查询( sqlFiddle )

OP asked for a view, but View in mySQL doesn't allow variables so I tried to write a different query (not using variables) such as this one (sqlFiddle)

SELECT T4.emp_id,T4.status1,T4.timestamp1,T4.status2,T4.timestamp2, T4.status3,T4.timestamp3,T4.status4,T4.timestamp4,T4.status5,T4.timestamp5, 'out' as status6, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T4.timestamp5 AND a.emp_id = T4.emp_id AND a.status = 'out') as timestamp6 FROM (SELECT T3.emp_id,T3.status1,T3.timestamp1,T3.status2,T3.timestamp2, T3.status3,T3.timestamp3,T3.status4,T3.timestamp4, 'in' as status5, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T3.timestamp4 AND a.emp_id = T3.emp_id AND a.status = 'in') as timestamp5 FROM (SELECT T2.emp_id,T2.status1,T2.timestamp1,T2.status2,T2.timestamp2, T2.status3,T2.timestamp3, 'out' as status4, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T2.timestamp3 AND a.emp_id = T2.emp_id AND a.status = 'out') as timestamp4 FROM (SELECT T1.emp_id,T1.status1,T1.timestamp1,T1.status2,T1.timestamp2, 'in' as status3, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T1.timestamp2 AND a.emp_id = T1.emp_id AND a.status = 'in') as timestamp3 FROM (SELECT a1.emp_id,a1.status as status1,a1.timestamp as timestamp1, 'out' as status2, (SELECT min(timestamp) as timestamp2 FROM overallrec a2 WHERE a2.timestamp > a1.timestamp AND a2.emp_id = a1.emp_id AND a2.status = 'out') as timestamp2 FROM overallrec a1 WHERE a1.status = 'in' AND NOT EXISTS (SELECT 1 FROM overallrec e WHERE e.timestamp < a1.timestamp AND e.emp_id = a1.emp_id AND DATE_FORMAT(e.timestamp,'%Y-%m-%d') = DATE_FORMAT(a1.timestamp,'%Y-%m-%d')) )T1 )T2 )T3 )T4;

不幸的是，mySQL视图不允许子查询(子查询不能在视图的FROM子句中使用.) 但是mySQL允许在视图之上创建视图，所以这里是创建的视图 ( sqlFiddle )

Unfortunately, mySQL views don't allow subqueries (Subqueries cannot be used in the FROM clause of a view.) but what mySQL allow is to create Views on top of views so here is the views created (sqlFiddle)

CREATE VIEW T100 AS SELECT a1.emp_id,a1.status as status1,a1.timestamp as timestamp1, 'out' as status2, (SELECT min(timestamp) as timestamp2 FROM overallrec a2 WHERE a2.timestamp > a1.timestamp AND a2.emp_id = a1.emp_id AND a2.status = 'out') as timestamp2 FROM overallrec a1 WHERE a1.status = 'in' AND NOT EXISTS (SELECT 1 FROM overallrec e WHERE e.timestamp < a1.timestamp AND e.emp_id = a1.emp_id AND DATE_FORMAT(e.timestamp,'%Y-%m-%d') = DATE_FORMAT(a1.timestamp,'%Y-%m-%d')); CREATE VIEW T200 AS SELECT T1.emp_id,T1.status1,T1.timestamp1,T1.status2,T1.timestamp2, 'in' as status3, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T1.timestamp2 AND a.emp_id = T1.emp_id AND a.status = 'in') as timestamp3 FROM T100 AS T1; CREATE VIEW T300 AS SELECT T2.emp_id,T2.status1,T2.timestamp1,T2.status2,T2.timestamp2, T2.status3,T2.timestamp3, 'out' as status4, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T2.timestamp3 AND a.emp_id = T2.emp_id AND a.status = 'out') as timestamp4 FROM T200 AS T2; CREATE VIEW T400 AS SELECT T3.emp_id,T3.status1,T3.timestamp1,T3.status2,T3.timestamp2, T3.status3,T3.timestamp3,T3.status4,T3.timestamp4, 'in' as status5, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T3.timestamp4 AND a.emp_id = T3.emp_id AND a.status = 'in') as timestamp5 FROM T300 AS T3; CREATE VIEW myFinalView AS SELECT T4.emp_id,T4.status1,T4.timestamp1,T4.status2,T4.timestamp2, T4.status3,T4.timestamp3,T4.status4,T4.timestamp4,T4.status5,T4.timestamp5, 'out' as status6, (SELECT min(timestamp) FROM overallrec a WHERE a.timestamp > T4.timestamp5 AND a.emp_id = T4.emp_id AND a.status = 'out') as timestamp6 FROM T400 AS T4;

所以有一个名为:myFinalView

这里是带有注释的myFinalView(请在VIEW中在此带注释的 sqlFiddle进行检查)

Here's a myFinalView with remarks (check this sqlFiddle with remarks in the VIEW)