SQL刷题笔记day6-1

1从不订购的客户

分析：从不订购，就是购买订单没有记录，not in

我的代码：

select c.name as 'Customers'
from Customers c
where c.id not in (select o.customerId from Orders o)

2  部门工资最高的员工

分析：每个部门（group by），薪资最高（排序取第一 or max函数），员工

我的错误代码：

select  d.name 'Department' ,e.name 'Employee' ,e.salary 'Salary' 
from Employee e join Department d on e.departmentId = d.id
group by d.id
order by e.salary

正确代码：按照(部门id和最大薪资)进行查询，这样才能保证找出同部门可能存在的多个最高薪资的员工。

select  d.name 'Department' ,e.name 'Employee' ,e.salary 'Salary' 
from Employee e join Department d on e.departmentId = d.id
where (e.departmentId,e.salary) in #找每个部门最高薪资的（可能不止一个）
(select departmentId,max(salary) from Employee 
group by departmentId)

 3 删除重复的电子邮箱

分析：重复的好找，如何删除？select——delete

我的代码：delete不会用 第一次，好像是delete中不能用分组函数group by?

delete id,email
from Person
group by id
having count(email)>1

 为什么这样也不行呢：

delete from Person
where id in 
(select id
from Person
group by id
having count(email)>1)

官方答案：from后调用自身表两次，email相等，但是重复的前面id不等，不等则删掉

delete p1
FROM Person p1,Person p2
WHEREp1.Email = p2.Email AND p1.Id > p2.Id

 

好了好了不卷了