一些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、删除重复邮件
7、查找重复的邮件
select Email
from Person
group by Email
having count(*) > 1
8、收入超过经理的员工
9、第二大的数据
select max(Salary) as SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee)
最新文章
- setTimeout 和 throttle 那些事儿
- vi(Visual Editor)常用的命令
- ZooKeeper设置ACL权限控制
- dg
- Delphi中window消息截获的实现方式(1)
- 委托--delegate
- php 常用五种模式
- 链表与Hash检索实测
- poj:2992 因子数量
- Windows 8 动手实验系列教程 实验5:进程生命周期管理
- Linux c readdir是非线程安全,需用readdir_r,要注意用静态变量当做返回值的函数的非线程安全性
- Spring boot中使用Mongodb
- 【HAOI2008】硬币购物
- std::set
- Oracle 11gR2使用RMAN duplicate复制数据库
- beta(3/7)
- mysql 清空数据
- python 时间元组转时间戳
- 如何在程序中使用CString
- 异步和等待(async和await)