复杂的选择查询，用于简单的输入/输出记录 [英] Complicated select query for a simple in/out record
问题描述
我正在制作一个简单的超时和超时系统.我有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不允许使用变量，因此我尝试编写一个不同的查询(不使用变量)，例如这样的查询(
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)
这篇关于复杂的选择查询，用于简单的输入/输出记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
