SQL查询技巧精讲-优快云博客

方法一：排除法，找到最大值，排除它，再找最大值
select max(Salary) as SecondHighestSalary  from Employee
    where Salary < 
    (select max(Salary) from Employee);
方法二:偏移法
select distinct Salary as SecondHighestSalary from Employee
    order by Salary desc
    limit 1
    offset 1
还需要考虑null的情况
select IFNULL(
    select distinct Salary as SecondHighestSalary from Employee
        order by Salary desc
        limit 1
        offset 1),  null) as SecondHighestSalary  ;

2. 超过经理收入的员工

首先需要将经理和员工摆在同一行,然后再进行判断

select a.Name from employee a 
    left  join 
    employee b
    on a.ManagerId  = b.Id;
    where 
        a.Salary  > b.Salary;

3. 查找重复的电子邮箱

方法一:利用group by

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

方法二:利用子查询

select Email from
	(
		select Email, count(Email) as num
		from Person
		group by Email) as a
where a.num > 1;

4. 从不订购的客户

某网站包含两个表，Customers表和Orders表，编写一个SQL查询，找出所有从不订购任何东西的客户


select a.Name as Customers from Customer a
	left join 
		Orders b
	on
		a.Id = b.CustomerId
	where 
		b.CustomerId is null;

5.上升的温度

给定一个Weather表，编写一个SQL查询，来查找与之前(昨天的)日期相比温度更高的所有日期的Id.

select 
	a.id 
from 
	weather a
inner join 
	weather b
on 
	a.Temperature > b.Temperature
where 
	datediff(a.RecordDate, b.RecordDate) = 1

6.求平均薪水的等级最低的部门的部门名称

第一步:求部门的平均水平

select
	e.deptno, e.dname, avg(e.sal) as avgsal
from 
	emp e
group by
	e.deptno

第二步：将以上结果当成临时表(deptno, avgsal)与salgrade表进行连接:t.avgsal between s.losal and s.hisal;求出平均薪水的等级

select 
	t.deptno, t.dname, s.grade
from
	t
join 
	salgrade s
on 
	t.avgsal between s.losal and s.hisal;

第三步:先求出最低等级再关联t表

select 
	t.dname
from
	t
where 
	t.salgrade = (
		select 
			min(t.salgrade) as minGrade
		from
			t
	)

7.取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名

第一步：找出普通员工（员工代码没有出现在mgr列上的）

select 
	* 
from
	emp
where 
	empno
not in
	(
		select 
			destinct mgr 
		from 
			emp 
		where 
			mgr is not null;

	)

第二步:找到普通员工的最高薪水

select max(sal) as maxsal from emp where empno not in (select destinct mgr from emp where mgr is not null);

第三步:找到最终结果

select 
    ename 
from 
    emp 
where 
    sal > (select max(sal) as maxsal from emp where empno not in(select destinct mgr from emp where mgr is not null));

8.取得薪水最高的前5名员工

select 
	*
from
	emp
order by
	sal desc
limit 0, 5;

9.取得薪水最高的第六到第十名员工

select 
	*
from
	emp
order by
	sal desc
limit 5, 5;

10. 取得最后入职的5名员工

select  * from emp order by hiredate desc limit 5;

11. 取得每个薪水等级有多少员工

第一步:查询出每个员工的薪水等级

select 
	e.ename, s.grade
from
	emp e
join
	salgrade s
on 
	e.sal between s.losal and s.hisal;

第二步:将以上查询结果当成临时表t(ename, grade)

select
	t.grade, count(t.ename) as totalEmp
from
	t
group by
	t.grade;

12.列出所有员工及领导的名字(将两张表进行关联) 因为要查询所有员工，所以应该使用left join

select
	e.ename,
	b.ename as leadername
from
	emp e
left join
	emp b
on 
	e.mgr = b.empno;

13.列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称

select 
    e.empno,  e.ename,  d.dname
from
    emp e
join 
    emp b
on
    e.mgr = b.empno
join
    dept d
on
    e.deptno = d.deptno
where
    e.hiredate < b.hiredate;

14.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select
    d.dname,
    e.*
from 
    emp e
right join
    dept d
on
    e.deptno = d.deptno

15. 列出至少有5个员工的所有部门

先求出每个部门的员工数量，然后继续通过having完成数据的过滤

select
    e.deptno, count(e.ename) as totalEmp
from 
    emp e
group by
    e.deptno;
having
    totalEmp >= 5;

16.列出薪水比”SMITH”多的所有员工信息（主要考察子查询)

第一步：找到员工姓名为SMITH的薪资

select
    sal
from
    emp
where 
    ename = ‘SMITH’

第二步:找到符合条件的员工信息

select 
    *
from
    emp
where
    sal > (select
                sal
            from
                emp
            where 
                ename = ‘SMITH’
    );

17. 列出所有”CLERE”(办事员)的姓名及其部门名称，部门人数

第一步:先找到所有”CLERE”(办事员)的姓名及其部门名称

select
    d.deptno, d.dname, e.ename
from
    emp e
join 
    dept d
on
    e.deptno = d.deptno
where
    e.job = ‘CLERE’;

把以上结果当成t1

第二步: 求出每个部门的员工数量,并当成t2表

select
    e.deptno, count(e.ename) as totalEmp
from
    emp e
group by 
    e.deptno;

第三步:将两张表进行连接,直接进行替换即可

select
    t1.deptno, t1.dname, t1.ename, t2.totalEmp
from 
    t1
join 
    t2
on
    t1.deptno = t2.deptno

18. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数

select
    e.job,
    min(sal) as minSal,
    count(e.ename) as totalEmp
from
    emp e
group by
    e.job
having 
    minSal > 1500;

19. 列出在部门”SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号

先找到销售部的部门编号，然后再查找需要的信息

select
    ename
from
    emp
where 
    deptno = (select
                    deptno
               from
                   dept
                where dname = ‘SALES);

20. 列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级

select 
    e.ename,
     d.dname, 
    b.ename as leadername,
    s.grade
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno
left join //注意是所有员工，需要使用left join
    emp b
on 
    e.mgr = b.empno
join
    salgrade s
on  
    e.sal between s.losal and hisal;
where 
    e.sal > (select avg(sal) as minSal from emp);