一些sql查询的case

1、单列去重，输出去重后条目数量

select count(distinct(`id`)) from student;

 

2、根据分数段统计数据条目：利用case when　　

select
count(case when move_num between 1 and 5 then 0 end) as 1到5,
count(case when move_num between 6 and 10 then 0 end) as 6到10
from cg_user_location_0;

 

3、部门最高薪水

SELECT d.Name as Department, e.Name as Employee, e.Salary

from Department d, Employee e
where e.DepartmentId = d.Id and e.Salary = (Select max(Salary) from Employee e2 where e2.DepartmentId = d.Id)

 

4、数据排名

SELECT
Score,
(SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
ORDER BY Score desc

 

5、There is a table courses with columns: student and class。Please list out all classes which have more than or equal to 5 students.

select class from courses 

group by class

having count(distinct student) >= 5//必须有distinct， 表明是5个不同的学生

 

6、删除重复邮件

delete p1

from Person p1, Person p2

where p1.Email = p2.Email

 and  p1.Id > p2.Id

 

 7、查找重复的邮件

select Email

from Person

group by Email

having count(*) > 1

 

8、收入超过经理的员工

select E1.name as Employee

from Employee as E1, Employee as E2

where E1.ManagerId = E2.Id and E1.salary > E2.salary

 

9、第二大的数据

select max(Salary) as SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee)