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
好了好了不卷了