Table structure such as

The results to be like this


Can use SQL directly write?

CodePudding user response:

As

 with t(select 'zhang' names, 'at 10:30 btime,' 11:30 etime, 'a' type, '2016/11/1 rq from dual union all 
Select 'zhang' names, '12:30 btime,' and 'etime,' a 'type,' 2016/11/1 rq from dual union all 
Select 'zhang' names, 'yea btime,' 11:30 etime, 'a' type, '2016/11/3 rq from dual union all 
Select 'zhang' names, '18:30 btime,' 7:30 etime, 'a' type, '2016/11/4 rq from dual) 
The select a.n Ames, a. time, a.e time, a.t ype, b.b time, b.e time, b.t ype from 
(select m. *, row_number () over (partition by names order by rq, btime) rn 
The from t m where rq='2016/11/1) a 
Full outer join 
(select m. *, row_number () over (partition by names order by rq, btime) rn 
The from t m where rq='2016/11/3) b 
On a.n Ames=b.n Ames and a.r n=b.r n 
; 

Very trouble, give you an idea, help you wrote 2 days

CodePudding user response:

Very trouble, give you an idea, help you wrote 2 days,
Every day you have to create a child query
The (select m. *, row_number () over (partition by names order by rq, btime) rn
The from t m where rq='2016/11/1'), and then the second subquery associated rq='2016/11/2', with a full outer join associations, a.n Ames=b.n Ames and a.r n=b.r n match,

Other only 2 days with a full outer join, if you are then connected to the third day, there will be some problems, so you will need a lot of nested sub-queries and,

CodePudding user response:

refer to the second floor js14982 response:

very troublesome, and give you an idea, help you wrote 2 days,
Every day you have to create a child query
The (select m. *, row_number () over (partition by names order by rq, btime) rn
The from t m where rq='2016/11/1'), and then the second subquery associated rq='2016/11/2', with a full outer join associations, a.n Ames=b.n Ames and a.r n=b.r n match,

Other only 2 days with a full outer join, if you are then connected to the third day, there will be some problems, so that you will need a lot of fruit, query and nested

Hungry, it is super trouble, not only when writing code nested loop, just feel like efficiency is very low, a user's information will be 31 days, 31 times, check, mama ah,
100 users will not be crazy

CodePudding user response:

To establish a temporary table
The table traversal again, the data inserted into a temporary table
And then directly to query statistics temporary table

CodePudding user response:

reference 4 floor sych888 response:

set up a temporary table
The table traversal again, the data inserted into a temporary table
And then directly on temporary table query statistics

How temporary table design table structure to facilitate the statistical effect? Under the guidance of the can

CodePudding user response:

reference 5 floor u011809117 reply:

Quote: refer to 4th floor sych888 response:

To establish a temporary table
The table traversal again, the data inserted into a temporary table
And then directly on temporary table query statistics

How temporary table design table structure to facilitate the statistical effect? Can you under the guidance of the

The structure of the temporary table is your report structure, intuitive and clear

CodePudding user response:

Above is type attendance related tables, and not your own name, possible name repetition, must have a staff list, with staff ID, the following table will staff ID substitution, for reference only, only wrote five days of data
- build table
The create table test_xz
(
Id varchar (20),
The name varchar (20),
Start_time varchar (20),
End_time varchar (20),
Type varchar (20),
Date_1 varchar (20)
)

- prepare data
Insert into test_xz values (' 1001 ', 'zhang', 'at 10:30,' at 'and' a ', '2016/11/1');
Insert into test_xz values (' 1002 ', 'zhang', 'made', 'and', 'a', '2016/11/1');
Insert into test_xz values (' 1001 ', 'zhang', 'yea,' forces', 'a', '2016/11/3');
Insert into test_xz values (' 1003 ', 'bill', 'at 10:30,' at 'and' a ', '2016/11/4');
Insert into test_xz values (' 1004 ', 'Cathy', 'at 10:30,' at 'and' a ', '2016/11/5');
Insert into test_xz values (' 1005 ', 'Cathy', 'and', 'passion' and 'a', '2016/11/5');
commit;
Query -
The select t.i d,
Min (t1) start_time) 1,
Max (t1) end_time) 1 end,
Max (t1) type),
Min (t2) start_time) 2,
Max (t2) end_time) 2 end,
Max (t2) type),
Min (t3. Start_time) 3,
Max (t3. End_time) end of the no. 3,
Max (t3) type),
Min (t4) start_time) 4,
Max (t4) end_time) 4 end,
Max (t4) type),
5 min (t5 start_time),
Max (t5, end_time) 5 end,
Max (t5. Type)

The from (select id, start_time, end_time, type the from test_xz) t
Left the join (select id, start_time, end_time, type
The from test_xz
Where date_1='2016/11/1) t1 data - 1
On t.i d=t1. Id
Left the join (select id, start_time, end_time, type
The from test_xz
Where date_1='2016/11/2) t2 on t.i d=t2. Id - 2 data
Left the join (select id, start_time, end_time, type
The from test_xz
Where date_1='2016/11/3) t3 on t.i d=t3. Id - 3 data
Left the join (select id, start_time, end_time, type
The from test_xz
Where date_1='2016/11/4) t4 on t.i d=t4. Id - 4 data
Left the join (select id, start_time, end_time, type
The from test_xz
Where date_1='2016/11/5) t5 on t.i d=t5. Id - 5 data

Group by t.i d
The order by t.i d;