# 【MySQL】第N高、排名等练习

## 第N高薪水

1. 自连接或笛卡尔积，连接条件为a表salary小于等于b表salary

2. 根据a表薪水字段分组，统计a表中每个salary分组后对应b表中salary唯一值个数，count（distinct b.salary）

3. having步骤2中的count =N，即实现了该分组中表1salary排名为第N个

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.

select a.Salary as 'getNthHighestSalary(N)'
from employee a,employee b
where  a.Salary <= b.Salary
GROUP BY a.Salary
having count(DISTINCT b.Salary)=N

);
END


### 各部门第2高薪水

1. 自连接或笛卡尔积，连接条件为a表salary小于等于b表salary
2. 因为是各部门所以要部门ID相等，不然会和其他部门比较
3. 根据a表名字字段分组，薪水不可以
4. 统计b表薪水字段的count，记得要去重
5. Having count（）=2
SELECT
a.DepartmentId,
a.Salary,
a.NAME
FROM
employee a,
employee b
WHERE
a.Salary <= b.Salary
AND a.DepartmentId = b.DepartmentId
GROUP BY
a.NAME
HAVING
count( DISTINCT b.Salary ) =2


SELECT
c.Name as 'departmentName',
a.Salary,
a.NAME
FROM
employee a,
employee b ,
department c
WHERE
a.Salary <= b.Salary
AND a.DepartmentId = b.DepartmentId
and a.DepartmentId=c.Id
GROUP BY
a.NAME
HAVING
count( DISTINCT b.Salary ) =2


## 部门工资最高的员工

1. 找出表1各部门薪水最高，使用分组+max（）
2. 两表相联，where in 第一步的部门id 和薪水
SELECT
department.NAME,
employee.NAME,
employee.Salary
FROM
employee
JOIN department ON employee.DepartmentId = department.Id
WHERE
( employee.DepartmentId, employee.Salary ) IN ( SELECT DepartmentId, max( Salary ) FROM employee GROUP BY employee.DepartmentId )


1. 自连接或笛卡尔积，连接条件为a表salary小于等于b表salary
2. 因为是各部门所以要部门ID相等，不然会和其他部门比较
3. 根据a表名字字段分组，薪水不可以
4. 统计b表薪水字段的count，记得要去重
5. Having count（）=1
SELECT
c.Name as 'Department',
a.NAME  as 'Employee',
a.Salary

FROM
employee a,
employee b ,
department c
WHERE
a.Salary <= b.Salary
AND a.DepartmentId = b.DepartmentId
and a.DepartmentId=c.Id
GROUP BY
a.NAME
HAVING
count( DISTINCT b.Salary ) =1


## 分数排名

1. 自连接或笛卡尔积，连接条件为a表s分数小于等于b表分数
2. 根据a表ID字段分组
3. 统计b表分数字段的count，记得要去重
4. 根据分数倒序
select a.Score,count(distinct b. Score) as 'Rank'
from Scores a ,Scores b
where a.Score<=b.Score
group by a.Id
order by a.Score desc


02-23 3043
04-06 979

04-29 1277
10-29 7856
11-03 96
04-06 882
02-05 21万+
03-06 275
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客