Recently looking at books about oracle, SQL queries, met with some don't understand don't understand the way of thinking, please can you point out the key, thank you

 drop table job_history; 
The create table job_history (
Employee_id number not null, 
The start_date date, 
End_date date, 
Ename varchar2 (30), 
Job varchar2 (30), 
Job_id number 
); 

The delete from job_history; 
Insert into job_history values (107, to_date (' 2014-07-07 ', '- dd yyyy - mm), to_date (' 2015-07-07', '- dd yyyy - mm), polly,' sales', 1); 
Insert into job_history values (107, to_date (' 2015-07-07 ', '- dd yyyy - mm), to_date (' 2018-07-07', '- dd yyyy - mm), polly,' finance ', 2); 
Insert into job_history values (104, to_date (' 2014-06-07 ', '- dd yyyy - mm), to_date (' 2015-06-07', '- dd yyyy - mm'), 'hanmeimei', 'secrectory, 3); 
Insert into job_history values (104, to_date (' 2015-06-07 ', '- dd yyyy - mm), to_date (' 2017-06-07', '- dd yyyy - mm'), 'hanmeimei', 'finance', 2); 
Insert into job_history values (105, to_date (' 1985-06-07 ', '- dd yyyy - mm), to_date (' 1987-02-07', '- dd yyyy - mm),' litao ', ' ' ', 4); 
Insert into job_history values (105, to_date (' 1987-06-07 ', '- dd yyyy - mm), to_date (' 2000-02-07', '- dd yyyy - mm),' litao ', 'operations person', 5); 
Insert into job_history values (4, to_date (' 2003-06-07 ', '- dd yyyy - mm), to_date (' 2006-04-07', '- dd yyyy - mm'), 'lilei', 'manager', 6); 
Insert into job_history values (11, to_date (' 2006-08-07 ', '- dd yyyy - mm), to_date (' 2009-04-07', '- dd yyyy - mm'), 'lilei', 'CEO, 7); 
Insert into job_history values (5, to_date (' 1999-01-07 ', '- dd yyyy - mm), to_date (' 1999-08-07', '- dd yyyy - mm'), 'uncle wang', 'the cto, 8). 
Insert into job_history values (12, to_date (' 2009-05-07 ', '- dd yyyy - mm), to_date (' 2010-08-07', '- dd yyyy - mm'), 'uncle wang', 'sales', 9); 
Insert into job_history values (6, to_date (' 2001-07-07 ', '- dd yyyy - mm), to_date (' 2005-06-07', '- dd yyyy - mm'), 'Jim,' police ', 10); 
Insert into job_history values (13, to_date (' 2005-07-07 ', '- dd yyyy - mm), to_date (' 2009-10-07', '- dd yyyy - mm'), 'Jim,' sales', 1); 
Insert into job_history values (7, to_date (' 2004-09-07 ', '- dd yyyy - mm), to_date (' 2008-06-07', '- dd yyyy - mm'), 'green', 'waiter', 11); 
Insert into job_history values (14, to_date (' 2008-09-07 ', '- dd yyyy - mm), to_date (' 2015-06-07', '- dd yyyy - mm'), 'green', 'waiter', 11); 
Insert into job_history values (1, to_date (' 2016-02-03 ', '- dd yyyy - mm), to_date (' 2016-09-03', '- dd yyyy - mm'), 'lilei', 'reporter, 12); 
Insert into job_history values (105, to_date (' 2016-02-03 ', '- dd yyyy - mm), to_date (' 2016-09-03', '- dd yyyy - mm),' litao ', 'reporter, 12); 
Insert into job_history values (104, to_date (' 2016-02-03 ', '- dd yyyy - mm), to_date (' 2016-09-03', '- dd yyyy - mm'), 'hanmeimei', 'reporter, 12); 
Insert into job_history values (104, to_date (' 2016-11-03 ', '- dd yyyy - mm), to_date (' 2017-10-03', '- dd yyyy - mm'), 'hanmeimei', 'model, 13); 

A:
The final result to query: employees worked in their work life of the same staff list:
1. Ordinary table since join queries is easy to understand
. second way, the biggest fixed number of year is equal to the minimum length of this method does not understand, or group by the data structure of is what?
2:




1. The first method, but also good understanding
2. second way, it is based on ideas or direction?

The above content from "proficient in oracle," second edition, chapter 4, interested can look at

CodePudding user response:

You say the second way is to point to which the SQL statement?

CodePudding user response:

reference 1st floor qq646748739 response:

you said second way is to point to which the SQL statement?

Said is the manner in which this paper according to the collection of SQL is given, and two questions are the second short SQL

CodePudding user response:

The first table is a staff job history table,
For example:
Zhang SAN development
1 yearZhang SAN director 1 year
Zhang SAN product manager
1 year
Li si development
1 yearLi si leader for 2 years
Li si director 1 year

If the employee group, if his post fixed number of year is the same, the maximum value is equal to the minimum, such as zhang SAN

CodePudding user response:

The second form, according to the first writing SQL can be concluded that the customer's order is continuous and single. The nature can be (Max - min)/quantity;
But this table design is very bad, is rare in reality.

CodePudding user response:

Not only for a single continuous, how can use this formula to calculate casually, logic no problem

CodePudding user response:

reference 5 floor acen_chen reply:

, not only for a single continuous, how can use this formula to calculate casually, logic no problem

This depends on how the business, generally speaking, each order is a cycle, and then calculate the average each order cycle;
For example:
Order order, order completion time
Order 1 2017/1/1 2017/1/10
Order 2 2017/1/1 2017/1/15
This kind of business, is to calculate the cycle of each order first, then the AVG,
For example of the building Lord, is more like the order without the concept of cycle, the demand for a period of time, the average how many days to produce one order,

CodePudding user response:

The

reference 3 floor chengccy response:

the first table is a historical tables, office employees
For example:
Zhang SAN development
1 yearZhang SAN director 1 year
Zhang SAN product manager
1 year
Li si development
1 yearLi si leader for 2 years
Li si director 1 year

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull