MySQL小练习

最新推荐文章于 2024-01-19 20:22:37 发布

竹马追风

最新推荐文章于 2024-01-19 20:22:37 发布

阅读量174

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/fu_daxian/article/details/102937857

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

分数排名

在这里插入图片描述


select a.Score as Score,
    (select count(distinct b.Score) from Scores b where b.Score>=a.Score) as Rank
    -- 创建临时表，（a b两表相同）查找b表中大于等于a表中的数据条数（用distinct去重），即为等级
    from Scores a
    order by Score DESC;

查找重复的邮箱

在这里插入图片描述

注:
顺序：where > group by > having > order by

--方法一 group by + 临时表
select Email 
    from (select Email,count(Email) as num from Person group by Email) as a
    --嵌套select要有别名
    where num>1

--方法二：group by + having
select Email
	from Person
	group by Email
	having count(Email) > 1;

各部门工资最高的员工

在这里插入图片描述


-- 先用group by + max()查询出各部门最高的工资和部门id
select d.Name as 'Department',e.Name as 'Employee',Salary
    from Employee e join Department d on e.DepartmentId=d.Id
    where (DepartmentId,Salary) in (select DepartmentId,max(Salary) from Employee group by DepartmentId)

删除重复的电子邮箱

在这里插入图片描述


-- 方法一：not in
DELETE FROM Person
	WHERE Id NOT IN (  
	select Id from   -- 要使用临时表，MySQL不允许select和delete操作同一个表
       (SELECT MIN(Id) AS Id FROM Person GROUP BY Email) as a
)
-- 方法二：
DELETE p1 FROM Person p1,Person p2
	WHERE p1.Email = p2.Email AND p1.Id > p2.Id