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

题目来源于leecode

第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