Day6: Brush 1 SQL every day, don’t believe your interview is still hanging on SQL

Find the salary of all employees at the time of entry, give emp_NO and salary, and reverse the order according to emp_NO (please note that an employee may have multiple salary increases)

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL.PRIMARY KEY (`emp_no`));
Copy the code

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL.PRIMARY KEY (`emp_no`,`from_date`));
Copy the code

😊 : “Reading the question, the salary at the time of entry, plus the prompt that an employee has received multiple salary increases, so essentially solving the minimum salary of each employee, and also according to emp_no descending order.

Ah, the minimum salary of each employee can be achieved by using Group by. The fields to be output are all in the table salaries. Therefore, although he gave two tables, only one table is needed to calculate the result. The answer:

select emp_no,
       min(salary)
from salaries
group by emp_no
order by emp_no desc
Copy the code

😄 : kuo! Do you know of any other solutions?

Here is the positive solution

1. Screen by minimum salary

select emp_no,
       min(salary)
from salaries
group by emp_no
order by emp_no desc
Copy the code

2. Screen by entry time

select emp_no,salary
from salaries
group by emp_no
having min( from_date )
order by emp_no desc
Copy the code

We can also solve it in this way.

The employee information sheet records the entry time of each employee, and the payroll record sheet records the starting and ending time of each payment.

So for the first salary paid to new employees after their entry, the start time should be equal to the entry time, and the end time should be the end time of the salary accounting cycle. Therefore, this can be achieved through the association of two tables.

select t1.emp_no,t1.salary
from salaries t1 inner join employees t2
on t1.emp_no = t2.emp_no and t1.from_date = t2.hire_date
order by t1.emp_no desc
Copy the code

Today’s Knowledge