数据库 SQL 语句问题，请大家帮帮忙

[jiaqidianbo]

如图所示

看似挺简单的SQL题目，但是我不知道怎么写这语句，请高手们多多解答，谢谢。

就是要用前两张表做出第三张表，我大致的思路是用join，然后group by，但做不出~~~

http://i.imgur.com/CCoOdXB.jpg?1

[bosshida]

select D.name as Department, E.Name as Employee, E.Salary as Salary
from Employee E, Department D
where E.DepartmentId = D.Id
group by E.DepartmentId
order by E.Salary
limit 3;

[wmttom]

leetcode database #185 应该是这道题，database 题目里唯一一个hard难度的，属于 聚合 topN 类的问题，MySQL 不是非常好实现
https://leetcode.com/problems/department-top-three-salaries/

我原来做的答案，写的比较乱，期待更高效的答案
SELECT c.Name AS Department,b.Name AS Employee, b.Salary AS Salary
FROM (
SELECT *,@rn := if(@did = DepartmentId, if(@ss=Salary,@rn,@rn + 1) , 1) AS rn,@did := DepartmentId,@ss:=Salary
FROM Employee,(SELECT @did:='',@rn:=0,@ss:=null) AS vars
ORDER BY DepartmentId,Salary DESC
) AS b
INNER JOIN Department AS c ON b.DepartmentId=c.Id
WHERE b.rn<=3
ORDER BY b.DepartmentId,b.Salary DESC

[jianghu52]

这个没那么复杂吧。我用union就搞定了啊。可能效率不是太好吧。

(SELECT name,salary, (case departid when 1 then 'IT' WHEN 2 then 'salers' else 'IT' END)as depart from employee where departid = 1 ORDER BY salary desc LIMIT 3) UNION
(SELECT name,salary, (case departid when 1 then 'IT' WHEN 2 then 'salers' else 'IT' END)as depart from employee where departid = 2 ORDER BY salary DESC LIMIT 3)

我直接就没用第二张表，比较无赖。

[wmttom]

@jianghu52 这类题目的测试用例经常会有多个一样的值，top three 不一定是3个，可能有并列排名。

[20150517]

select * from (
select *, d,Name, rank(salary) OVER (PARTITION BY e.departmentid) rank
from employee e
inner join department d on d.id=e.departmentid) x
where r<=3

其实很简单,关键是个rank函数
http://www.postgresql.org/docs/9.1/static/tutorial-window.html

[20150517]

上面随便draft的,有些错误,应该是按salary倒顺序,看懂就ok

[mhycy]

SELECT
Department,
Name,
Salary
FROM (
SELECT
DepartmentId,
Department.Name AS Department,
employee.Name,
Salary
FROM
Employee
JOIN Department ON Department.Id = Employee.DepartmentId
) AS tmp
WHERE
Salary IN (
SELECT Salary FROM Employee WHERE DepartmentId = tmp.DepartmentId GROUP BY Salary ORDER BY Salary DESC LIMIT 0,3
)
ORDER BY DepartmentId ASC, Salary DESC

如果mysql子查询能用limit应该可以这么写

[fangjinmin]

我这个已经执行过，可以保证是正确的。

select department.name, employee.name, salary from employee, department where employee.departmentid = department.id and (select count(*) from employee as s where s.departmentid = employee.departmentid and s.salary >= employee.salary ) <=3 order by employee.departmentid, employee.salary desc;

[mhycy]

@fangjinmin
测试正确...
才知道还能这么用...
但对于并列情况也许还能加个GROUP BY...

[jiaqidianbo]

@wmttom 我想问一下，代码中的@表什么意思，还有c.Name种的c表示什么？
(SELECT @did:='',@rn:=0,@ss:=null) AS vars 这句语句我也没懂，冒号的作用。。
本人mysql初学者，感谢解答~~

[wmttom]

@jiaqidianbo @ 表示用户变量， 在 select 中赋值用 := ，MySQL 没有 rank，相当于自己模拟一个，http://stackoverflow.com/questions/3333665/rank-function-in-mysql