我正在在和时间进行系统的一个简单的时间。 我有3对/退出。
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行中的第一个是另一个日期，但接下来的状态时间戳，并很快从以前的日期重复。 我希望它显示为空时，仍然是空的，而不是复制以前的日期值。 换句话说，它应该产生在日变化的另一行。
补充说明：当我在每一个输入添加说明，它会在表中的结果连接起来。 我想它是这样的：
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"
我应该怎么做我的查询？ 或者，如果不与查询，还有什么？
更改答案，我忘了通过timestamp1订购，这里，将采取多关心员工的最终版本:) 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联接的利用当天检查的工作，然后再逐个输出第二天
OP问的看法，但在查看MySQL不允许变量，所以我试着写（不使用变量）不同的查询，如这一个（ 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的观点不允许子查询（子查询不能在使用从视图中的条款。），但什么MySQL的允许是创造的意见，让这里俯视图是创建的视图（ 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与言论（检查这sqlFiddle与视图的话 ）
你可以尝试添加一个group by到您的查询：
group by a1.emp_id, date(a1.timestamp), date(a2.timestamp), date(a3.timestamp),
date(a4.timestamp), date(a5.timestamp), date(a6.timestamp)
说实话，虽然，我会做整个查询与“in”和“出”的一行有条件的聚集。 这将工作一对，但不是三个。 我不明白你为什么要在一行三双。 怎么样的员工，只有有两对在一天？ 或者谁有四对？
