如何查找重复数据
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
解题思路:
(1)看到查询重复,就要想到用group by分组,然后再用count计数。本题是对Email进行分组,然后对Email计数;
(2)分组汇总后,生成如下的表,再选出count>1的Email
计数 | |
---|---|
a@b.com | 2 |
c@d.com | 1 |
- 方法一
- 创建一个辅助表,将Email进行分组汇总
select Email ,count(Email) as 计数
from Person
group by Email;
- 选出辅助表中计数>1的Email
select Email
from 辅助表
where 计数>1;
- 将以上两个表合为一个表
select Email
from(select Email,count(Email) as 计数
from Person
group by Email)
where 计数>1;
- 方法二
不用辅助表,直接用having子句对分组查询结果进行筛选。
不能直接将count语句直接放到where子句中,因为where不能和聚合函数连用
select Email
from Person
group by Email
having count(Email) >1;
- 所有的汇总函数会在计算之前将null值排除在外再计算
- count 求某列有多少行,count() 括号内加*即可不排除null直接计算
- sum 对某列数据求和,只能对数值类型的列计算
- avg 求某列数据的平均值,只能对数值类型的列计算
- max 求某列数据的最大值
- min 求某列数据的最小值
1.代码如下:
SELECT COUNT(教师姓名)-- 求教师姓名这一列的行数,count会在计算时将含有null的排除在外
from teacher;
select count(*) -- 括号内加*可将含null的行也计算进去
FROM teacher;
select sum(成绩) -- 总分
from score;
select avg(成绩) -- 平均值
from score;
select max(成绩),min(成绩) -- 成绩这一列的最大值与最小值
from score;
select count(distinct 姓名)-- 在列名前加disdinct可去除重复值之后再计算
from student;
-- 查询课程编号为'0002'的总成绩
select sum(成绩)
from score
where 课程号 = '0002';
-- 查询选了课程的学生人数
select count(distinct 学号) as '选课人数'
from score;
2.分组
sql分组:group by
-- 运行顺序
select 性别,count(*) as '学生人数' -- 4
from student -- 1
where 出生日期 > '1990-01-01' -- 2
group by 性别; -- 3
-- 查询各科成绩最高和最低的分
select 课程号,max(成绩),min(成绩)
from score
where 课程号 in ('0001','0002','0003')
group by 课程号;
-- 查询每门课程被选修的学生数
select 课程号,count(学号) as '选课人数'
from score
where 课程号 in ('0001','0002','0003')
group by 课程号;
-- 查询男生,女生人数
select 性别,count(*)
from student
group by 性别;
3.对分组结果指定条件
having对分组结果指定条件
-- 只取出男生组
select 性别,count(*)
from student
group by 性别
having count(*) >1; -- 对分组结果指定条件,选取行数大于1的组
/*
select 查询结果
from 从那张表中查找数据
where 查询条件
group by 分组
having 对分组结果指定条件
*/
-- 查询平均成绩大于60分学生的学号和平均成绩
select 学号,avg(成绩) as 平均成绩
from score
group by 学号
having avg(成绩)>60;
-- 查询至少选修两门课程的学生学号
select 学号,count(课程号)
from score
group by 学号
having count(课程号) >=2;
-- 查询两名同性学生名单并同名人数
select 姓名,count(*)
from student
group by 姓名
having count(*) >1;
4.对查询结果排序
desc(从大到小) asc(从小到大)
-- desc(从大到小) asc(从小到大)
select*
from score
order by 成绩 asc,课程号 desc; -- 先按第一个列名排序,若第一个列名中存在相同的值,则再按第二个列名排序
-- null的排序
select*
from teacher
order by 教师姓名; -- null会排在最前面
-- 从查询结果中取出指定行
select*
from score
limit 2;
-- 查询不及格的课程并按课程号从大到小排列
select 课程号
from score
group by 课程号
having min(成绩) <60
order by 课程号 desc;
select 课程号
from score
where 成绩<60
order by 课程号 desc;
-- 查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同的,按课程号降序排列
select 课程号,avg(成绩)
from score
group by 课程号
order by avg(成绩) asc,课程号 desc;
5.如何看懂报错信息
由于select子句的运行顺序在group by, having等字句之后,group by, having子句中不能使用select中确定的别名;
where子句中不能使用汇总函数;
6.总结