【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
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页