Я делаю простое время и тайм-аут системы. У меня есть 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
Обратите внимание, что первая строка «in» timestamp value 2 – это другая дата, но следующая временная метка состояния и скоро повторяется с предыдущей даты. Я хочу, чтобы он отображал значение null, когда все еще пусто, а не копировать значение на предыдущую дату. Другими словами, он должен генерировать еще одну строку в день изменения.
Дополнительные замечания: когда я добавляю замечания на каждом входе, он будет объединен в результат таблицы. Я хотел, чтобы это было так:
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')
Однако этот запрос не будет работать, если сотрудник заработает через один день, а затем выйдет на следующий день, потому что он использует проверку того же дня, чтобы ЛЕВАТЬ ПРИСОЕДИНЕНИЕ
OP запросил представление, но View в 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 не позволяют подзапросы (подзапросы не могут использоваться в предложении FROM представления.), Но то, что разрешено 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;
поэтому там у нас есть VIEW :), называемый myFinalView
Вот myFinalView с замечаниями (проверьте этот sqlFiddle с замечаниями в VIEW )
Вы можете попробовать добавить 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» и «out» в одной строке. Это будет работать для одной пары, но не для трех. Я не понимаю, почему вы хотите, чтобы три пары были в одной строке. Как насчет сотрудников, у которых есть только две пары за один день? Или у кого четыре пары?