MySQL小练习

电玩女神 2023-05-29 13:25 9阅读 0赞

#### 分数排名 ####

![在这里插入图片描述][20191106163211967.png]

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;

#### 查找重复的邮箱 ####

![在这里插入图片描述][20191107112043305.png]

注:
    顺序：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;

#### 各部门工资最高的员工 ####

![在这里插入图片描述][20191107204329270.png]

-- 先用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)

#### 删除重复的电子邮箱 ####

![在这里插入图片描述][20191107210401638.png]

-- 方法一：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

[20191106163211967.png]: https://img-blog.csdnimg.cn/20191106163211967.png
[20191107112043305.png]: https://img-blog.csdnimg.cn/20191107112043305.png?
[20191107204329270.png]: https://img-blog.csdnimg.cn/20191107204329270.png
[20191107210401638.png]: https://img-blog.csdnimg.cn/20191107210401638.png?