Sql commands using joins for the following four tables
There are four tables开发者_开发知识库
- Employee (Employee_id,name,chief_id)
- Department(Department_Id,name)
- Emp_Dep(Employee_id,Department_Id)
- Emp_Sal(Employee_id,salary)
Now I need to write a query which displays Employee IDs who get maximum salary in each department.
You don't say which flavour of database you are using (unless you intend SQL = SQL-Server). So this solution pay not work for you. Any, it uses Oracle's analytic function RANK() to solve the problem.
Given this data ...
SQL> select d.name
2 , e.name
3 , es.salary
4 from employee e join
5 emp_sal es on (es.employee_id = e.employee_id) join
6 emp_dep ed on (ed.employee_id = e.employee_id) join
7 department d on (d.department_id = ed.department_id)
8 order by d.department_id, es.salary desc
9 /
NAME NAME SALARY
---------- ---------- ----------
PRODUCTION GRINCH 1100
PRODUCTION CAT 1100
DELIVERY LORAX 3000
DELIVERY DAISY 2500
ACCOUNTS FOX 5000
ACCOUNTS KNOX 2500
ACCOUNTS SAM-I-AM 1200
7 rows selected.
SQL>
Notice the tie in PRODUCTION, which is why this returns four rows ...
SQL> select dept_name
2 , emp_name
3 , salary
4 from (
5 select d.name as dept_name
6 , e.name as emp_name
7 , es.salary
8 , rank () over (partition by d.department_id
9 order by es.salary desc ) as rnk
10 from employee e join
11 emp_sal es on (es.employee_id = e.employee_id) join
12 emp_dep ed on (ed.employee_id = e.employee_id) join
13 department d on (d.department_id = ed.department_id)
14 )
15 where rnk = 1
16 order by salary desc
17 /
DEPT_NAME EMP_NAME SALARY
---------- ---------- ----------
ACCOUNTS FOX 5000
DELIVERY LORAX 3000
PRODUCTION GRINCH 1100
PRODUCTION CAT 1100
SQL>
edit
I used employee names rather than IDs because it makes the results easier to read. It is easy enough for you to substitute your desired projectors.
This will give you the value of maximum salary:
SELECT
department_id, MAX(es.salary) AS max_salary
FROM
emp_sal es JOIN emp_dep ed ON ed.employee_id = es.employee_id
GROUP BY ed.department_id
This will give you the ID:
SELECT department_id, employee_id, max_values.max_salary
FROM
emp_sal es JOIN emp_dep ed ON ed.employee_id = es.employee_id
JOIN
(SELECT
department_id, MAX(es.salary) AS max_salary
FROM
emp_sal es JOIN emp_dep ed ON ed.employee_id = es.employee_id
GROUP BY ed.department_id) AS max_values
ON (ed.department_id = max_values.department_id AND es.salary = max_salary)
Creating my own test tables:
DECLARE @Employee TABLE (Employee_id INT, Name VARCHAR(MAX), Chief_id INT)
DECLARE @Department TABLE (Department_Id INT, Name VARCHAR(MAX))
DECLARE @Emp_Dep TABLE (Employee_id INT, Department_Id INT)
DECLARE @Emp_Sal TABLE (Employee_id INT, Salary DECIMAL)
INSERT INTO @Employee
SELECT 1, 'John Doe', 0 UNION ALL
SELECT 2, 'John Doe', 0 UNION ALL
SELECT 3, 'John Doe', 0 UNION ALL
SELECT 4, 'John Doe', 0 UNION ALL
SELECT 5, 'John Doe', 0 UNION ALL
SELECT 6, 'John Doe', 0 UNION ALL
SELECT 7, 'John Doe', 0;
INSERT INTO @Department
SELECT 1, 'Moomin' UNION ALL
SELECT 2, 'Moo' UNION ALL
SELECT 3, 'Min';
INSERT INTO @Emp_Dep
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 3 UNION ALL
SELECT 6, 3 UNION ALL
SELECT 7, 3;
INSERT INTO @Emp_Sal
SELECT 1, 1200 UNION ALL
SELECT 2, 1100 UNION ALL
SELECT 3, 2000 UNION ALL
SELECT 4, 2000 UNION ALL
SELECT 5, 3451 UNION ALL
SELECT 6, 3321 UNION ALL
SELECT 7, 3123;
This version will all people in a department if they are tied :)
WITH [Merged] AS (
SELECT [@Employee].[Employee_id],
[@Employee].[Name],
[@Department].[Name] [Department],
[@Department].[Department_Id],
[@Emp_Sal].[Salary]
FROM @Employee
INNER JOIN @Emp_Sal ON [@Employee].[Employee_id] = [@Emp_Sal].[Employee_id]
INNER JOIN @Emp_Dep ON [@Emp_Sal].[Employee_id] = [@Emp_Dep].[Employee_id]
INNER JOIN @Department ON [@Emp_Dep].[Department_Id] = [@Department].[Department_Id]
)
SELECT [Main].Employee_id, [Main].Name, [Main].Department, [Main].Salary
FROM Merged [Main]
WHERE [Salary] = (SELECT MAX(Salary) FROM [Merged] WHERE Department_Id = [Main].Department_Id);
Whereas this version will only select 1 from each department, you'll have to figure out a way to get the person you want on top in the ROW_NUMBER() OVER
WITH [Merged] AS (
SELECT [@Employee].[Employee_id],
[@Employee].[Name],
[@Department].[Name] [Department],
[@Department].[Department_Id],
[@Emp_Sal].[Salary],
ROW_NUMBER() OVER(ORDER BY [@Department].[Department_Id] ASC, [@Emp_Sal].[Salary] DESC, [@Employee].[Employee_id] ASC) AS 'RowNumber'
FROM @Employee
INNER JOIN @Emp_Sal ON [@Employee].[Employee_id] = [@Emp_Sal].[Employee_id]
INNER JOIN @Emp_Dep ON [@Emp_Sal].[Employee_id] = [@Emp_Dep].[Employee_id]
INNER JOIN @Department ON [@Emp_Dep].[Department_Id] = [@Department].[Department_Id]
)
SELECT [Main].Employee_id, [Main].Name, [Main].Department, [Main].Salary
FROM Merged [Main]
WHERE [RowNumber] = (SELECT MAX(RowNumber) FROM [Merged] WHERE Department_Id = [Main].Department_Id);
Worth mentioning might be that it's for Microsoft SQL Server!
I think the following should work ..
[EDITED]
SELECT
ed.department_id,
d.[name],
es.employee_id,
e.[name],
es.salary
FROM
emp_sal es
INNER JOIN emp_dep ed
ON ed.employee_id = es.employee_id
INNER JOIN employee e
ON e.employee_id = ed.employee_id
INNER JOIN department d
ON d.department_id = ed.department_id
WHERE
es.salary = (
SELECT
max(iEs.salary)
FROM
emp_sal iEs
INNER JOIN emp_dep iEd
ON iEd.employee_id = iEs.employee_id
WHERE
iEd.department_id = ed.department_id
)
I have made it a bit more complex in order to display name of employee and department..
For just the employee ID use
SELECT
es.employee_id
FROM
emp_sal es
INNER JOIN emp_dep ed
ON ed.employee_id = es.employee_id
WHERE
es.salary = (
SELECT
max(iEs.salary)
FROM
emp_sal iEs
INNER JOIN emp_dep iEd
ON iEd.employee_id = iEs.employee_id
WHERE
iEd.department_id = ed.department_id
)
select
e.employee_id
d.name as department,
max(s.salary) as msalary
from
department as d
right join emp_dep as e on e.department_id = d.department_id
left join emp_sal as s on s.employee_id = e.employee_id
group by d.name
or something like that
加载中,请稍侯......
精彩评论