select ifnull((select distinct Salary from Employee order by Salary desc limit 1, 1), null) as SecondHighestSalary
子查询去重查找第二高的薪水,如果为空用ifnull的第二个参数(null),查询结果起别名
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN set N = N - 1; RETURN ( # Write your MySQL query statement below. select ifnull((select distinct Salary from Employee order by Salary desc limit N, 1), null) as getNthHighestSalary ); END
参数N-1 再和上面一样进行查询
select a.Score, count(distinct(b.Score)) as 'Rank' from Scores a join Scores b on b.Score >= a.Score group by a.Id order by a.Score desc;
连表查询,查出去重后b中分数大于等于a的条数进行计数,用a.id进行分组保证结果条数
select distinct l.Num as ConsecutiveNums from Logs l join Logs o join Logs g on (l.id = o.id - 1 and l.Num = o.Num) and (o.id = g.id - 1 and o.Num = g.Num)
连接查询三次,比较相邻的id的Num,三个连续相同的就查询出来,将结果去重保留一个
select d.Name as 'Department', e.Name as 'Employee', e.Salary as 'Salary' from Employee e join Department d on e.DepartmentId = d.Id and (e.DepartmentId,e.Salary) in (select DepartmentId, max(Salary) from Employee group by DepartmentId)
使用子查询按部门id分组查询出每个部门中最高的工资,查询出表中雇员是否是对应部门最高的工资
select s1.id as 'id', coalesce(s2.student, s1.student) as 'student' from seat s1 left join seat s2 on (s1.id = s2.id - 1 and s1.id % 2 = 1) or (s2.id = s1.id - 1 and s2.id % 2 = 1)