Oracle processes the attendance time, splits the SQL statement of the attendance period, and oraclesql

Oracle processes the attendance time, splits the SQL statement of the attendance period, and oraclesql

I have been using mysql databases for cloud projects recently, but I haven't been in touch with oracle for some time. Yesterday, a friend asked me to help me deal with an attendance record with oracle, it took me some time to think about how to streamline implementation as much as possible. So we will record this implementation.

The requirements are as follows:

It can be combined with other tables to form such a display:

 

Personnel	Date	Working hours	Factory entry time	Factory time
Zhang San		9 hours 30 minutes	8:00:00	10:30:00
			11:00:00	12:30:00
			13:30:00	17:30:00
			18:00:00	18:30:00
			19:00:00	19:30:00
			20:00:00	20:30:00
Li Si	...	...	..	..

What I want to achieve is how to split the data in the Time column into two columns for display:

The original column is as follows:

 

The effect of splitting is as follows:

The implementation method is as follows:

First, create a time table (of course, here I only pay attention to the scan_time field, and ignore other fields ):

create table CMIS_PERSON_FI_DETAIL_TB(  detail_id    NUMBER(20) not null,  scan_time    DATE,  person_fk_id NUMBER(20) not null)

Second, after data is inserted, the effect is as follows:

Finally, write an SQL statement to implement it. The SQL statement is as follows:

select max(a) in_time, max(b) out_time  from (select decode(mod(rownum, 2), 1, scan_time) a,               decode(mod(rownum, 2), 0, scan_time) b,               round(rownum / 2) c          from CMIS_PERSON_FI_DETAIL_TB) group by c order by c;

Effort! Come on!