SQL习题总结

复用表格进行比较排名

对所有员工的薪水按照salary进行按照1-N的排名

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

本题的主要思想是复用salaries表进行比较排名，具体思路如下：

1、先对一张表的salary进行排序

select  emp_no ，salary
from salaries
where to_date = '9999-01-01'
order by salary desc;

2、进行并列操作，加入 count

select  emp_no ，salary，count（salary）
from salaries
where to_date = '9999-01-01'
order by salary desc;

3、这样只是统计了，这个salary出现的次数，并没有依照次序进行排序，要进行次序的排序，必须count,大于等于该条salary的数据条数，又因为数据有重复，所以distinct，此处必须使用表的重复使用功能

select  a.emp_no ，a.salary，
count（distinct b.salary）
from salaries as a,salaries as b
where a.to_date = '9999-01-01'
and b.to_date ='9999-01-01'
and a.salary<= b.salary
order by salary desc;

3、因为使用了合计函数导致，count只返回一个值，表a选择返回的值却有好几个，所以必须进行分组查询

select  a.emp_no ，a.salary，
count（distinct b.salary）
from salaries as a,salaries as b
where a.to_date = '9999-01-01'
and b.to_date ='9999-01-01'
and a.salary<= b.salary
group by a.emp_no
order by salary desc;

4、最后在s1.salary 逆序排之后，再以 s1.emp_no 顺序排列输出结果，必须满足第一个条件的情况下，满足第二个排序条件，等于进行的是相同的rank，数据有重复的值进行了emp_no的排序

select  a.emp_no ，a.salary，
count（distinct b.salary）
from salaries as a,salaries as b
where a.to_date = '9999-01-01'
and b.to_date ='9999-01-01'
and a.salary<= b.salary
group by a.emp_no
order by a.salary desc，a.emp_no asc;

重复元素

182. 查找重复的电子邮箱

inner join

select distinct(p1.Email) as 'Email'
from person p1 inner join person p2
on p1.email = p2.email and p1.id != p2.id

Group by + Having

select Email
from Person
group by Email
having count(Email) > 1;

未出现的元素

183. 从不订购的客户

NOT IN

select customers.name as Customers
from customers
where customers.id not in
(
    select customerid from orders
);

LEFT JOIN

select A.Name as Customers
from Customers A left join Orders B
on A.Id = B.CustomerId
where B.Id is null;

分组求极值，前几名

184. 部门工资最高的员工

Group by 求分组最高，INNER JOIN 合并信息

select
    department.name Department,
    employee.name employee,
    employee.salary salary
from 
    employee
    inner join Department
on employee.departmentid = department.id
where
    (employee.salary,employee.departmentid)
    in
    (
        select max(salary),departmentid
        from employee
        group by departmentid  
    )

185. 部门工资前三高的所有员工

3> count()

• *前 3 高的薪水，意思是不超过三个值比这些值大*

SELECT
    Department.NAME AS Department,
    e1.NAME AS Employee,
    e1.Salary AS Salary 
FROM
    Employee AS e1,Department 
WHERE
    e1.DepartmentId = Department.Id 
    AND 3 >  (SELECT  count( DISTINCT e2.Salary ) 
             FROM   Employee AS e2 
             WHERE  e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId     ) 
ORDER BY Department.NAME,Salary DESC;

having count(distinct ...)<2

• 先group by然后用having count()来筛选

  • 用Employee和自己做连接，连接条件是【部门相同但是工资比我高】

  • 按照having count(Salary) <= 2来筛选的原理是：如果【跟我一个部门而且工资比我高的人数】不超过2个，那么我一定是部门工资前三，这样内层查询可以查询出所有符合要求的员工ID，接下来外层查询就简单了。

• 工资降序DESC

select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee as e left join Department as d 
on e.DepartmentId = d.Id
where e.Id in
(
    select e1.Id
    from Employee as e1 left join Employee as e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.Id
    having count(distinct e2.Salary) <= 2
)
and e.DepartmentId in (select Id from Department)
order by d.Id asc,e.Salary desc