1.超过经理收入的员工

1
2
3
4
5
6
7
8
SELECT
*
FROM
Employee AS a, (新颖)
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary

2.删除重复数据

1
delete a from Person a,Person b where a.Email = b.Email and a.id > b.id	

3.返回两个日期之间的天数(DATEDIFF)

1
SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate

4.取第二高的薪水

offset X 是跳过X个数据 limit Y 是选取Y个数据

1
2
3
4
5
6
7
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary

5.实现分数排名

确定一个分数的排名,就是大于此分数的分数去重后的个数

1
2
3
4
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC