如何根据场和条件将列分成几列 [英] How Can I Split A Column Into Few Columns Depending On A Field And Conditions

问题描述

正在创建基于考勤系统的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

推荐答案

在一个真实的数据库中，这可以用等级和数据透镜解决。

但是由于MySQL既不支持，而且不是CTE（Aargh）。

你必须使用这样的结构：

On a real database this could have been solved using a rank and a pivot.
But since MySQL doesn't support neither, and also not CTEs (Aargh).
You have to use a construct like this:

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.

这篇关于如何根据场和条件将列分成几列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！