如何在不覆盖sql中的数据的情况下对数据集进行分组 [英] how to group data set without overwrite data in sql

问题描述

我尝试按以下顺序设置员工出勤日期.

I try to set of the employee attendance date prepare by the following order.

row data

emp_no  date                time
001   2019-08-07 00:00:00    1899-12-30 08:29:28
001   2019-08-07 00:00:00    1899-12-30 17:29:28

我想准备像样并保存在花药表中

I want to prepare like and save in anther table

emp_no    date      time_in   time_out
001     2019-08-07 08:29:28   17:29:28

time in 03:00 am - 12:15 pm
time out 12:15 pm to 03:00 am 

行数据通过指纹机获取. 每天记录2000名员工的详细信息.如何解决此问题?我尝试根据emp_no和date分组，但得到错误的输出 我正在使用php和MySQL进行开发

row data get by finger print machine. 2000 employee detail are record by day. how to solve this problem?i try to group by according emp_no and date but get wrong output I am using php and MySQL for development

推荐答案

这可能有帮助.

1. 如果您需要保存在新表中，请首先创建new_table
2. 在主表的new_table中插入格式化的数据
3. 从new_table中选择所有数据

    create table new_table(emp_no number,
                           day_date date,
                           time_in varchar2(15),
                           time_out varchar2(15));

    insert into new_table 
    select emp_no,
           date,
           extract(hour from min(time)) || ':' || extract(minute from min(time)) || ':' || extract(second from min(time)),
           extract(hour from max(time)) || ':' || extract(minute from max(time)) || ':' || extract(second from max(time))
      from employee
    group by emp_no, date;

    select * from new_table;

尽管我不确定您从数据中得出的是什么

Although I am not sure what you are deriving below data

time in 03:00 am - 12:15 pm
time out 12:15 pm to 03:00 am 

这篇关于如何在不覆盖sql中的数据的情况下对数据集进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！