我正在尝试编写一个查询,以获得2019年全年金额最高和第二低的名称(工资+变量(以及相应的金额值。
Employees Table:
| Emp_id | first_name | Last_name | Salary | Joining_date | Department|
|:------ |:----------:|:---------:|:------:|:-----------------:|:---------:|
| 001 | Manish | Agarwal | 700000 |2019-04-20 09:00:00| HR |
| 002 | Niranjan | Bose | 20000 |2019-02-11 09:00:00| DA |
| 003 | Vivek | Singh | 100000 |2019-01-20 09:00:00| DA |
| 004 | Asutosh | Kapoor | 700000 |2019-03-20 09:00:00| HR |
| 005 | Vihaan | Banerjee | 300000 |2019-06-11 09:00:00| DA |
| 006 | Atul | Diwedi | 400000 |2019-05-11 09:00:00| Account |
| 007 | Sathyendra | Tripathi | 95000 |2019-03-20 09:00:00| Account |
| 008 | Prithika | Bhatt | 95000 |2019-03-20 09:00:00| DA |
Variables table:
| Emp_Ref_ID | Variables_Date | Variables_amount|
|:---------- |:------------------:|:---------------:|
| 1 |2019-02-20 00:00:00 | 15000 |
| 2 |2019-06-11 00:00:00 | 30000 |
| 3 |2019-02-20 00:00:00 | 42000 |
| 4 |2019-02-20 00:00:00 | 14500 |
| 5 |2019-06-11 00:00:00 | 23500 |
Designation table:
| Emp_Ref_ID | Emp_title | Affected_from |
|:---------- |:--------------:|:------------------:|
| 1 | Asst.Manager |2019-02-20 00:00:00 |
| 2 | Senior Analyst |2019-01-11 00:00:00 |
| 8 | Senior Analyst |2019-04-06 00:00:00 |
| 5 | Manager |2019-10-06 00:00:00 |
| 4 | Asst.Manager |2019-12-06 00:00:00 |
| 7 | Team Lead |2019-06-06 00:00:00 |
| 6 | Team Lead |2019-09-06 00:00:00 |
| 3 | Senior Analyst |2019-09-06 00:00:00 |
我尝试了下面的查询,创建了一个名为t1的表,然后从中获取值
select emp_title , emp_ref_id from
(select d.emp_ref_id , d.emp_title, e.salary+v.variables_amount as full_amount from employees e join variables_details v on
e.emp_id = v.emp_ref_id join designation d on v.emp_ref_id=d.emp_ref_id) as t1
where t1.emp_ref_id in (select t1.emp_ref_id from t1 where full_amount = (select max(full_amount) from t1) or full_amount = (select min(full_amount)
from t1 limit 1 offset 1);
但是查询抛出了一个错误。
后来我决定创建一个临时表,然后从中提取数据
create temporary table temp (select d.emp_ref_id , d.emp_title, e.salary+v.variables_amount as full_amount from employees e join
variables_details v on e.emp_id = v.emp_ref_id join designation d on v.emp_ref_id=d.emp_ref_id);
select emp_title, full_amount from temp where full_amount = (select max(full_amount) from temp) or
full_amount = (select full_amount from temp order by full_amount asc limit 1 offset 1);
但它抛出了一个错误:;不能重新打开表temp";。
这两种方法对我都不起作用。
我应该如何处理这个问题?
正确的缩进有助于查找缺失的parentesis
在mysql8中,您可以使用With子句生成t1
查询#1
WITH t1 AS (select d.emp_ref_id , d.emp_title, e.salary+v.variables_amount as full_amount
from employees e join variables_details v on e.emp_id = v.emp_ref_id
join designation d on v.emp_ref_id=d.emp_ref_id)
select emp_title , emp_ref_id
from
t1
where t1.emp_ref_id in (select
t1.emp_ref_id
from t1
where full_amount = (select max(full_amount) from t1)
or full_amount = (select min(full_amount)
from t1 limit 1 offset 1)
);
emp_title | emp_ref_id |
---|---|
助理经理 | 1 |