mysql的sql语句示例

最新推荐文章于 2019-02-23 13:30:25 发布

xiaoniuxqq

最新推荐文章于 2019-02-23 13:30:25 发布

阅读量208

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/u011649691/article/details/79429375

mysql 专栏收录该内容

49 篇文章

订阅专栏

本文通过具体实例介绍了如何使用SQL查询来找出每个部门薪资最高的员工，并展示了如何为员工表中的薪资进行排序，确保相同薪资的员工拥有相同的排名。

1、 The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+—-+——-+——–+————–+
| Id | Name | Salary | DepartmentId |
+—-+——-+——–+————–+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+—-+——-+——–+————–+

The Department table holds all departments of the company.

+—-+———-+
| Id | Name |
+—-+———-+
| 1 | IT |
| 2 | Sales |
+—-+———-+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+————+———-+——–+
| Department | Employee | Salary |
+————+———-+——–+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+————+———-+——–+
求：每个部门薪水最多的那个人，只一个人
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
)

2、在employee表中列出薪水的排序，同一薪水的人序号相同：
SELECT
salary,
@rank := @rank + (@prev <> (@prev := salary)) Rank
FROM
employee,
(SELECT @rank := 0, @prev := -1) init
ORDER BY salary desc