7

## [演算法][SQL]演算法挑戰系列(2)-Department Highest Salary

HI，這是演算法挑戰挑戰的第二篇，我這個人習慣有求必應，所以這篇就來解解SQL的題目吧！

`Employee`表內容：

Id Name Salary DepartmentId
1 Joe 70000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1

`Department`表內容：

Id Name
1 IT
2 Sales

Department Employee Salary
IT Max 90000
Sales Henry 80000

``````/*SELECT資料欄位*/
SELECT d.Name AS Department ,e.Name Employee ,s.salary
/*以部門為主檔*/
FROM Department d
LEFT JOIN
/*找出各部門的最高薪水*/
(SELECT MAX(Salary) Salary ,DepartmentId
FROM Employee
GROUP BY DepartmentId) s
/*用部門編號取得部門名稱*/
ON s.DepartmentId = d.Id
/*連結員工資料表*/
LEFT JOIN Employee e
/*用在哪個部門，以及該最高薪水找出對應的人名*/
ON e.DepartmentId = s.DepartmentId AND e.Salary=s.Salary
/*如果有該部門但沒有人員會回傳NULL，所以這時候把他過濾掉*/
WHERE s.salary IS NOT NULL
``````

### 7 則留言

1

iT邦高手 1 級 ‧ 2018-05-14 21:21:29

1
fysh711426
iT邦研究生 4 級 ‧ 2018-05-14 23:07:33

``````DECLARE @Employee TABLE
(
Id INT,
Name NVARCHAR(50),
Salary INT,
DepartmentId INT
);

DECLARE @Department TABLE
(
Id INT,
Name NVARCHAR(50)
);

INSERT INTO @Employee
(Id, Name, Salary, DepartmentId)
VALUES
(1, N'Joe', 70000, 1),
(2, N'Henry', 80000, 2),
(3, N'Sam', 60000, 2),
(4, N'Max', 90000, 1)

INSERT INTO @Department
(Id, Name)
VALUES
(1, N'IT'),
(2, N'Sales')

SELECT C.Name,
A.Name,
A.Salary
FROM @Employee AS A
LEFT JOIN (
SELECT A.DepartmentId,
MAX(A.Salary) AS Salary
FROM @Employee AS A
GROUP BY A.DepartmentId
) AS B ON B.DepartmentId=A.DepartmentId AND B.Salary=A.Salary
LEFT JOIN @Department AS C ON C.Id=A.DepartmentId
WHERE B.Salary IS NOT NULL
ORDER BY A.DepartmentId
``````

2

iT邦高手 2 級 ‧ 2018-05-15 11:37:48

``````declare @Employee table(
Id int
,Name nvarchar(50)
,Salary int
,DepartmentId int
)
insert into @Employee
values(1, N'Joe', 70000, 1),(2, N'Henry', 80000, 2),(3, N'Sam', 60000, 2),(4, N'Max', 90000, 1)

declare @Department table(
Id int
,Name nvarchar(50)
)
insert into @Department
values(1, N'IT'),(2, N'Sales')

select Name,Salary
from (
select Row_Number() Over(Partition by a.Id Order by b.Salary desc) as Sort
,a.Name
,b.Salary
from @Department as a
left join @Employee as b on a.Id = b.DepartmentId
) as k
where Sort = 1

``````

``````select a.Name
,isNull((
select top 1 Salary
from @Employee as b
where a.Id = b.DepartmentId
order by Salary desc
),0) as Salary
from @Department as a
``````

``````select a.Name
,isNull((
select max(Salary)
from @Employee as b
where a.Id = b.DepartmentId
),0) as Salary
from @Department as a
``````

``````declare @Detail table(
Department_Name nvarchar(50)
,Employee_Name nvarchar(50)
,Salary int
)

declare @i int,@Max int
declare @Index int,@Department_Name nvarchar(50)
declare @Employee_Name nvarchar(50),@Salary int
set @i = 1

select @Max = isNull(count(*),0)
from @Department

while(@i<=@Max)
begin
select @Index=Id
,@Department_Name=Name
from (
select Row_Number() Over(order by Id) as Sort
,Id
,Name
from @Department
) as k
where Sort = @i

select top 1
@Employee_Name=Name
,@Salary=Salary
from @Employee
where DepartmentId = @Index
order by Salary desc

insert into @Detail
values(@Department_Name,@Employee_Name,@Salary)

set @Employee_Name = ''
set @Salary = 0
set @i = @i +1
end

select * from @Detail

``````

``````SELECT
b.Name AS 'Department',
a.Name AS 'Employee',
Salary
FROM
@Employee as a
JOIN
@Department as b ON a.DepartmentId = b.Id
WHERE
exists(
select *
from @Employee as c
where c.DepartmentId = b.Id
having MAX(c.Salary) = a.Salary
)
``````

https://zh.wikipedia.org/wiki/Transact-SQL

@@..基本算是全方位工程師啦...系統架設、程式開發、美工製作都自己來..

1

iT邦新手 3 級 ‧ 2018-05-16 00:32:27

``````create table dept (
id int not null primary key
, name text not null
);

create table emp (
id int not null primary key
, name text not null
, salary int not null
, dept_id int not null references dept(id)
);

insert into dept values
(1, 'IT'), (2, 'Sales');

insert into emp values
(1, 'Joe', 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam', 60000, 2),
(4, 'Max', 90000, 1);

--
select dept_id
, first(name order by salary desc)
, first(salary order by salary desc)
from emp
group by dept_id;

dept_id | first | first
---------+-------+-------
1 | Max   | 90000
2 | Henry | 80000
(2 筆資料列)

select d.name Department
, a.employee
, a.salary
from (select dept_id
, first(name order by salary desc) employee
, first(salary order by salary desc) salary
from emp
group by dept_id) a
join dept d
on a.dept_id = d.id;

department | employee | salary
------------+----------+--------
IT         | Max      |  90000
Sales      | Henry    |  80000
(2 筆資料列)
``````

`first(salary order by salary desc)`

``````first(salary order by salary desc)
``````
``````(SELECT TOP 1 salary ORDER BY salary DESC)
``````

1
philoreiser
iT邦新手 5 級 ‧ 2018-05-16 02:26:39

``````CREATE TABLE IF NOT EXISTS `Employee` (
Id INT(8) NOT NULL AUTO_INCREMENT,
Name VARCHAR(52) NOT NULL,
Salary INT(8) NOT NULL,
DepartmentId INT(8) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Department` (
Id INT(8) NOT NULL AUTO_INCREMENT,
Name VARCHAR(52) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Employee`
(Name, Salary, DepartmentId) VALUES
('Joe', 70000, 1),
('Henry', 80000, 2),
('Sam', 60000, 2),
('Max', 90000, 1);

INSERT INTO `Department` (Name) VALUES ('IT'), ('Sales');

SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary FROM `Employee` AS e
RIGHT JOIN (
SELECT MAX(Salary) AS MaxSalary, DepartmentId AS DepId
FROM `Employee`
GROUP BY DepartmentId
) AS m ON e.Salary = m.MaxSalary AND e.DepartmentId = m.DepId
JOIN `Department` AS d ON e.DepartmentId = d.Id
ORDER BY Salary ASC;

``````

2
darwin0616
iT邦新手 3 級 ‧ 2018-05-16 10:39:00

``````SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
(   SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
``````

``````(Employee.DepartmentId , Salary) IN
(   SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
``````
1

iT邦新手 5 級 ‧ 2018-05-17 17:52:54

``````select d.Name Department,e.Name Employee,MAX(e.Salary) Salary from employee e inner join department d on e.DepartmentId = d.Id GROUP BY e.DepartmentId
``````