问题描述
正在创建基于考勤系统的Web应用程序。我正在使用MySQL和PHP。我为每位参加过考试的员工准备了一张桌子，当员工参加机器时，他们会不断添加。我提取了每天出勤2次的员工，我将他们添加到带有列的表中。现在，我想提取每天出勤1次或每天超过2次的员工的出勤率，这是错误的，我想将它们添加到员工时间1，时间2和时间3中。
我有一张桌子作为这个设计，它是一个考勤系统，
如果每天的出勤人数不等于2，我需要分摊时间，这是错误日志。
am creating a web application based on attendance system. I am using MySQL and PHP. I have a table for every employee that has took attendance and they keep adding when the employees takes attendance on the machine. I extracted the employees that has has attendance 2 times per day which are in and out, and I added them to a table with columns. Now I want to extract the attendances for the employees that has took attendance 1 per day or more than 2 per day which they are error and i want to add them to a table employee time1, time2 and time3.
I have a table as this design, it's an attendance system,
I need to split the time if the attendance count per day was not equal to 2, which are error logs.
id pd time date
1 5 07:05 08/07/2014
2 4 18:02 07/07/2014
3 1 07:05 06/07/2014
4 1 07:06 06/07/2014
5 1 18:00 06/07/2014
我需要将它们添加到数据库中的表中，并按照pd和日期分成该表格。
I need to add them to a table in the database and to be split in that form with respect the pd and date.
id pd time1 time2 time3 .... date
1 5 07:05 08/07/2014
2 4 18:02 07/07/2014
3 1 07:05 07:06 18:00 06/07/2014
抱歉，这是我一生中的第一篇文章，如果我做错了或不完整，请原谅我
sorry guys its my first post in my whole entire life , so forgive me if am doing something wrong or not complete
推荐答案
SELECT
pd,
date,
max(case rank when 1 then time else null end) as time1,
max(case rank when 2 then time else null end) as time2,
max(case rank when 3 then time else null end) as time3
FROM
(
SELECT
pd,
date,
time,
find_in_set
(
time,
(
select group_concat(time order by time)
from MyTable t2
where t1.pd = t2.pd
and t1.date=t2.date
)
) as rank
FROM MyTable t1
) as sub
GROUP BY pd,date如果你的成绩很高，表现会很糟糕。
Performance will be horrible if your sets are big.
这篇关于如何根据场和条件将列分成几列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！