牛客网sql经典版（较难程度）

最新推荐文章于 2022-08-04 19:21:01 发布
最新推荐文章于 2022-08-04 19:21:01 发布
阅读量447 收藏 6
点赞数 2
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38807606/article/details/113051638
版权

1.五星：count 函数实现  dense_rank() over()

# 五星：重要 : count() 函数实现 dense_rank() over()
# 74
# 每个性别分数排名前2的用户，得到的结果先按照的 先按照salary降序排序 ，然后name升序排序

select * from employees_59 ;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |垂        |男     |2021-01-23|
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# |4     |2021-01-23|峰         |萧        |男     |2021-01-23|
# |5     |2021-01-23|远山        |萧        |男     |2021-01-23|
# +------+----------+----------+---------+------+----------+
select * from salaries_60;
# +------+------+----------+----------+---+
# |emp_no|salary|from_date |to_date   |sex|
# +------+------+----------+----------+---+
# |1     |100   |2021-01-23|9999-01-01|0  |
# |2     |1000  |2021-01-23|9999-01-01|1  |
# |3     |10000 |2021-01-23|9999-01-01|1  |
# |4     |500   |2021-01-23|9999-01-01|0  |
# |5     |5000  |2021-01-23|9999-01-01|1  |
# +------+------+----------+----------+---+


# 每个性别分数排名前2的用户，得到的结果先按照的 先按照salary降序排序 ，然后name升序排序
# 思路一： 用分析函数，分组后内部排序然后取前两条数据
select
       s.salary,
        e.last_name,
       e.first_name,
       dense_rank() over (partition by s.sex order by  s.salary desc , e.last_name asc) as s_rn
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no;
# +------+---------+----------+----+
# |salary|last_name|first_name|s_rn|
# +------+---------+----------+----+
# |500   |萧        |峰         |1   |
# |100   |一光       |郭         |2   |
# |10000 |雪        |慕容        |1   |
# |5000  |萧        |远山        |2   |
# |1000  |垂        |慕容        |3   |
# +------+---------+----------+----+

# 上述sql 加限制条件   s_rn <=2 ,取得组内的排名前 二名 的数据
select
       s.salary,
        e.last_name,
       e.first_name,
       dense_rank() over (partition by s.sex order by  s.salary desc , e.last_name asc) as s_rn
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no and s_rn <=2;

# 识别不了别名，戴个套
select * from (
              select
       s.salary,
        e.last_name,
       e.first_name,
       dense_rank() over (partition by s.sex order by  s.salary desc , e.last_name asc) as s_rn
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no
                  ) A where A.s_rn <=2;
# ------+---------+----------+----+
# |salary|last_name|first_name|s_rn|
# +------+---------+----------+----+
# |500   |萧        |峰         |1   |
# |100   |一光       |郭         |2   |
# |10000 |雪        |慕容        |1   |
# |5000  |萧        |远山        |2   |
# +------+---------+----------+----+

# 每个性别分数排名前2的用户，得到的结果先按照salary降序排序 ，然后name升序排序
# 思路二：  count() 实现 dense_rank() over()

select s.salary,
       e.last_name,
       e.first_name,
       s.sex
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no group by  s.sex ;
# +------+---------+----------+---+
# |salary|last_name|first_name|sex|
# +------+---------+----------+---+
# |100   |一光       |郭         |0  |
# |1000  |垂        |慕容        |1  |
# +------+---------+----------+---+

select s.salary,
       e.last_name,
       s.sex
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no group by  s.sex ,s.salary, e.last_name;
# +------+---------+---+
# |salary|last_name|sex|
# +------+---------+---+
# |100   |一光       |0  |
# |1000  |垂        |1  |
# |10000 |雪        |1  |
# |500   |萧        |0  |
# |5000  |萧        |1  |
# +------+---------+---+

# 在此基础上 增加一列：排名(同一个组内部的排名，所以需要加上 s.sex = s2.sex 这个限制条件)
select s.salary,
       e.last_name,
       s.sex,
       (select count(distinct salary) from  salaries_60 s2 where  s.salary>= s2.salary and s.sex = s2.sex) as rn
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no   group by  s.sex ,s.salary, e.last_name ;

# 在此结果上 and   rn <=2 过滤数据
select * from (
    select s.salary,
       e.last_name,
       s.sex,
       (select count(distinct salary) from  salaries_60 s2 where  s.salary>= s2.salary and s.sex = s2.sex) as rn
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no   group by  s.sex ,s.salary, e.last_name
                  )A where A.rn <=2 ;
# +------+---------+---+--+
# |salary|last_name|sex|rn|
# +------+---------+---+--+
# |100   |一光       |0  |1 |
# |1000  |垂        |1  |1 |
# |500   |萧        |0  |2 |
# |5000  |萧        |1  |2 |
# +------+---------+---+--+


# count  的另一种写法：这个不需要戴套

# 先分组，不考虑组内前两名问题
select s.salary,
       e.last_name,
       s.sex
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no
group by  s.sex ,s.salary, e.last_name ;
# +------+---------+---+
# |salary|last_name|sex|
# +------+---------+---+
# |100   |一光       |0  |
# |1000  |垂        |1  |
# |10000 |雪        |1  |
# |500   |萧        |0  |
# |5000  |萧        |1  |
# +------+---------+---+
#
# 加上 where条件 ，限制同一组内的 前两名数据
select s.salary,
       e.last_name,
       s.sex
from employees_59 e
         inner join salaries_60 s on e.emp_no = s.emp_no
        where  (select count(distinct salary) from  salaries_60 s2 where  s.salary>= s2.salary and s.sex = s2.sex) <=2
group by  s.sex ,s.salary, e.last_name ;

1. 五星：（排序问题）

# 重要：五星
# 54 (难度范围：一般难度 但是思路很好：考察点：排序 dense_rank() over() ，和 count的转换，以及 count 的位置：select 后面 ； where count() <3 取前三名数据)
# 查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。
# 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`));

# 最大最下问题()排序问题 三个思路： ① 排除掉最大最下 ②count  ③分析函数
select * from salaries_60;
# +------+------+----------+----------+---+
# |emp_no|salary|from_date |to_date   |sex|
# +------+------+----------+----------+---+
# |1     |100   |2021-01-23|9999-01-01|0  |
# |2     |1000  |2021-01-23|9999-01-01|1  |
# |3     |10000 |2021-01-23|9999-01-01|1  |
# |4     |500   |2021-01-23|9999-01-01|0  |
# |5     |5000  |2021-01-23|9999-01-01|1  |
# +------+------+----------+----------+---+
#  思路一： 排除掉最大最下值

select avg(salary)
from salaries_60
where salary not in (select max(salary) from salaries_60 union all select min(salary) from salaries_60);
# +-----------+
# |avg(salary)|
# +-----------+
# |2166.6667  |
# +-----------+
# 确认下

select avg(salary)
from (
         select salary
         from salaries_60
         where salary not in (select max(salary) from salaries_60 union all select min(salary) from salaries_60)
     ) A;
# +-----------+
# |avg(salary)|
# +-----------+
# |2166.6667  |
# +-----------+
# or (范围查询)
select avg(salary) as avg_salary
from salaries_60
where salary <
(
select max(salary)
from salaries_60
where to_date = '9999-01-01'
)
and salary >
(
select min(salary)
from salaries_60
where to_date = '9999-01-01'
)
and to_date = '9999-01-01' ;

# 思路二：分析函数 rownum() over
select s.salary ,
        row_number() over (order by s.salary desc ) as rm
         from salaries_60 s ;
# +------+--+
# |salary|rm|
# +------+--+
# |10000 |1 |
# |5000  |2 |
# |1000  |3 |
# |500   |4 |
# |100   |5 |
# +------+--+
# 然后排除掉最大和最小的行数 ??
select salary
from (
         select s.salary,
                dense_rank() over (order by s.salary desc ) as rm

         from salaries_60 s
     ) A
# 第一 肯定 rm 值为  1
where rm > 1
  and rm < (
#                            子查询获得最大排名（rm  是个定值，定值的话考虑子查询来实现）
    select max(rm)
    from (
             select dense_rank() over (order by s.salary desc ) as rm
             from salaries_60 s
         ) B
);
# +------+
# |salary|
# +------+
# |5000  |
# |1000  |
# |500   |
# +------+

# 在此基础上取平均值
select avg(salary)
from (
         select s.salary,
                dense_rank() over (order by s.salary desc ) as rm

         from salaries_60 s
     ) A
# 第一 肯定 rm 值为  1
where rm > 1
  and rm < (
#                            子查询获得最大排名（rm  是个定值，定值的话考虑子查询来实现）
    select max(rm)
    from (
             select dense_rank() over (order by s.salary desc ) as rm
             from salaries_60 s
         ) B
);
# +-----------+
# |avg(salary)|
# +-----------+
# |2166.6667  |
# +-----------+



#错误sql： 识别不了 mm , 戴个套 (这个sql 有问题，结果不对,max 的位置不对)
select avg(salary) from (
              select salary ,rm  ,max(rm) from (
                   select s.salary ,
        dense_rank() over  (order by s.salary desc ) as rm
         from salaries_60 s group by  salary
                       ) A where rm > 1
                  )  B where  B.rm < max(rm) ;

# +-----------+
# |avg(salary)|
# +-----------+
# |5000.0000  |
# +-----------+

# 思路三： ②count

select s1.salary,
       (select count(distinct salary) from salaries_60 s2 where s1.salary <=s2.salary) as rm
from salaries_60 s1 where rm in (2,rm-1);
# 错误sql：识别不了别名 戴套
select * from (
              select s1.salary,
       (select count(distinct salary) from salaries_60 s2 where s1.salary <=s2.salary) as rm
# from salaries_60 s1 order by salary desc
#    或者下面语句(注释掉的和下面的是 等效的)
    from salaries_60 s1 order by rm
                  ) A  where rm in (2,rm-1);
# +------+--+
# |salary|rm|
# +------+--+
# |5000  |2 |
# +------+--+

#分析：错误原因：
# +------+--+
# |salary|rm|
# +------+--+
# |10000 |1 |
# |5000  |2 |
# |1000  |3 |
# |500   |4 |
# |100   |5 |
# +------+--+

# rm = 1 ，where rm in (2,rm-1) ---> where rm in (2,0) 不取
# rm = 2 ，where rm in (2,rm-1) ---> where rm in (2,1) 可以取到 rm = 2 这条数据
# rm = 3 ，where rm in (2,rm-1) ---> where rm in (2,2) 不取
# rm = 4 ，where rm in (2,rm-1) ---> where rm in (2,3) 不取
# rm = 5 ，where rm in (2,rm-1) ---> where rm in (2,4) 不取
# 综上 in 的话，就只能两个值，目的要的是多个 的范围值（between and ，大于等于小于 etc）

# 正确sql：
select *
from (
         select s1.salary,
                (select count(distinct salary) from salaries_60 s2 where s1.salary <= s2.salary) as rm
# from salaries_60 s1 order by salary desc
#    或者下面语句(注释掉的和下面的是 等效的)
         from salaries_60 s1
         order by rm
     ) A
where rm > 1
  and rm < (
#                       获取排名序号最大的
    select max((select count(distinct salary) cnt from salaries_60 s2 where s3.salary <= s2.salary)) as maxRn
    from salaries_60 s3
);
# +------+--+
# |salary|rm|
# +------+--+
# |5000  |2 |
# |1000  |3 |
# |500   |4 |
# +------+--+

# 在此基础上进行求平均值
select avg(salary)
from (
         select s1.salary,
                (select count(distinct salary) from salaries_60 s2 where s1.salary <= s2.salary) as rm
# from salaries_60 s1 order by salary desc
#    或者下面语句(注释掉的和下面的是 等效的)
         from salaries_60 s1
         order by rm
     ) A
where rm > 1
  and rm < (
#                       获取排名序号最大的（具体思路看下面的  一 ）
    select max((select count(distinct salary) cnt from salaries_60 s2 where s3.salary <= s2.salary)) as maxRn
    from salaries_60 s3
);
# +-----------+
# |avg(salary)|
# +-----------+
# |2166.6667  |
# +-----------+

#  （一）获取最大的序列号（排名号）
select  A.salary,cnt from (
         select s3.salary,
       (select count(distinct salary) cnt from salaries_60 s2 where s3.salary <=s2.salary) as cnt
    from salaries_60 s3 order by  cnt
                    ) A ;
# +------+---+
# |salary|cnt|
# +------+---+
# |10000 |1  |
# |5000  |2  |
# |1000  |3  |
# |500   |4  |
# |100   |5  |
# +------+---+

# 单纯的获取排名序号
select (select count(distinct salary) cnt from salaries_60 s2 where s3.salary <= s2.salary) as cnt
from salaries_60 s3
order by cnt;
# +---+
# |cnt|
# +---+
# |1  |
# |2  |
# |3  |
# |4  |
# |5  |
# +---+


# 简化sql
select  max(cnt) from (
         select
       (select count(distinct salary) cnt from salaries_60 s2 where s3.salary <=s2.salary) as cnt
    from salaries_60 s3 order by  cnt
                    ) A ;
# +--------+
# |max(cnt)|
# +--------+
# |5       |
# +--------+

# 再次简化（去掉套儿）
         select
       max((select count(distinct salary) cnt from salaries_60 s2 where s3.salary <=s2.salary) ) as maxRn
    from salaries_60 s3 ;
# +-----+
# |maxRn|
# +-----+
# |5    |
# +-----+




# or  count 的另一种写法（count 放到 where 后面）
select avg(salary)
from (
         select s1.salary

         from salaries_60 s1
     ) A
# 说明： (select count(distinct salary) from salaries_60 s2 where A.salary <= s2.salary) 这个sql是某行数据的排名号
where (select count(distinct salary) from salaries_60 s2 where A.salary <= s2.salary) > 1
  and (select count(distinct salary) from salaries_60 s2 where A.salary <= s2.salary) < (
#                       获取排名序号最大的（具体思路看下面的  一 ）
    select max((select count(distinct salary) cnt from salaries_60 s2 where s3.salary <= s2.salary)) as maxRn
    from salaries_60 s3
);
# +-----------+
# |avg(salary)|
# +-----------+
# |2166.6667  |
# +-----------+

# 可以简化（count 放到后面是直接作为条件的，不需要戴套儿）---> 最终sql


select avg(s1.salary)
from salaries_60 s1
# 说明： (select count(distinct salary) from salaries_60 s2 where A.salary <= s2.salary) 这个sql是某行数据的排名号
where (select count(distinct salary) from salaries_60 s2 where s1.salary <= s2.salary) > 1
  and (select count(distinct salary) from salaries_60 s2 where s1.salary <= s2.salary) < (
#                       获取排名序号最大的（具体思路看下面的  一 ）
    select max((select count(distinct salary) cnt from salaries_60 s2 where s3.salary <= s2.salary)) as maxRn
    from salaries_60 s3
);
# +--------------+
# |avg(s1.salary)|
# +--------------+
# |2166.6667     |
# +--------------+


# 思路四：聚合函数（技巧）
SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2)  as avg_salary
FROM salaries_60 where to_date = '9999-01-01';
# +----------+
# |avg_salary|
# +----------+
# |2166.6667 |
# +----------+

1.五星：(重要：思路很好)

count 函数实现 dense_rank() over()

# 五星：重要 count() 函数 如何实现 dense_rank() over() 的功能
# 61 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
CREATE TABLE `employees_61` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
# 如，输入为：
INSERT INTO employees_61 VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees_61 VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees_61 VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees_61 VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

select * from employees_61;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |10001 |1953-09-02|Georgi    |Facello  |M     |1986-06-26|
# |10002 |1964-06-02|Bezalel   |Simmel   |F     |1985-11-21|
# |10005 |1955-01-21|Kyoichi   |Maliniak |M     |1989-09-12|
# |10006 |1953-04-20|Anneke    |Preusig  |F     |1989-06-02|
# +------+----------+----------+---------+------+----------+

# 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
# 取模函数：mod（m，n），n为被除数
# 思路：
select first_name ,dense_rank() over (order by first_name)  as dr from employees_61 where mod(dr,2)!=0;

#因为识别不了别名，所以戴个套
select first_name from (
              select first_name ,dense_rank() over (order by first_name)  as dr from employees_61
                  ) A where   mod(dr,2)!=0;
# +----------+
# |first_name|
# +----------+
# |Anneke    |
# |Georgi    |
# +----------+

# 不用 mod 函数
select first_name from (
              select first_name ,dense_rank() over (order by first_name)  as dr from employees_61
                  ) A where   dr%2 = 1;
# +----------+
# |first_name|
# +----------+
# |Anneke    |
# |Georgi    |
# +----------+

# 思路三  ：涉及到排名  count() 函数 (此处不考虑 dense_rank() over )
select first_name ,
#        一定要去重，否则排名会有问题
       (select count(distinct first_name) from employees_61 e2 where e.first_name <= e2.first_name ) as rn
from employees_61 e where rn%2 = 1  ;
# 识别不了别名，戴个套

select first_name ,rn
from (
         select first_name,
#        一定要去重，否则排名会有问题
                (select count(distinct first_name) from employees_61 e2 where e.first_name >= e2.first_name) as rn
         from employees_61 e
     ) A
where A.rn % 2 = 1;
# +----------+--+
# |first_name|rn|
# +----------+--+
# |Georgi    |3 |
# |Anneke    |1 |
# +----------+--+

 

1.五星（重要：分析函数，新知识点）


#  由一个sql引入的分析函数 sum() over()
#  60
#  按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。
#  具体结果如下Demo展示。
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`));


select * from salaries_60;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |1     |100   |2021-01-23|9999-01-01|
# |2     |1000  |2021-01-23|9999-01-01|
# |3     |10000 |2021-01-23|9999-01-01|
# |4     |500   |2021-01-23|9999-01-01|
# |5     |5000  |2021-01-23|9999-01-01|
# +------+------+----------+----------+

# running_total

select s.emp_no,
       s.salary,
       row_number() over (order by emp_no asc)                                                        as rn,
       (select sum(s2.salary), row_number() over (order by emp_no asc) as rn2 from salaries_60 s2 where s2.rn2 <= s.rn) as running_total
from salaries_60 s;

# 识别不了别名 ，进行改写 （下面是个错误的sql）

select A.emp_no, A.salary, A.rn ,
       (
          select  B.sm from (
                         select sum(s2.salary) as  sm , row_number() over (order by emp_no asc) as rn2 from salaries_60 s2
                          ) B where B.rn2 <= A.rn

           )  as running_total
from (
         select s.emp_no,
                s.salary,
                row_number() over (order by emp_no asc) as rn
         from salaries_60 s
     ) A ;

# +------+------+--+-------------+
# |emp_no|salary|rn|running_total|
# +------+------+--+-------------+
# |1     |100   |1 |16600        |
# |2     |1000  |2 |16600        |
# |3     |10000 |3 |16600        |
# |4     |500   |4 |16600        |
# |5     |5000  |5 |16600        |
# +------+------+--+-------------+


#避免  is incompatible with sql_mode=only_full_group_by  这个错误
set session sql_mode='';
select  B.sm from (
 select sum(s2.salary) as  sm , row_number() over (order by emp_no asc) as rn2 from salaries_60 s2
                           ) B where B.rn2 <= 3 ;



select sum(s2.salary)  from salaries_60 s2;
# +--------------+
# |sum(s2.salary)|
# +--------------+
# |16600         |
# +--------------+
# 下面sql错误：sum(salary) 会对  salaries_60 表的所有数据求和，导致 B的  sm 是一个常量
select sm
from (
         select sum(salary) sm, row_number() over (order by emp_no asc) rn2 from salaries_60 s2
     ) B
where B.rn2 <= 2;
# +-----+
# |sm   |
# +-----+
# |16600|
# +-----+

# 正确解法
select sum(sm)
from (
         select salary sm, row_number() over (order by emp_no asc) rn2 from salaries_60 s2
     ) B
# 识别不了  s2  ，所以加个套 ，where B.rn2 <= 2
where B.rn2 <= 2;
# +-------+
# |sum(sm)|
# +-------+
# |1100   |
# +-------+

# 正确sql
select A.emp_no, A.salary, A.rn ,
       (
          select  sum(sm) from (
                         select s2.salary as  sm , row_number() over (order by emp_no asc) as rn2 from salaries_60 s2
                          ) B where B.rn2 <= A.rn

           )  as running_total
from (
         select s.emp_no,
                s.salary,
                row_number() over (order by emp_no asc) as rn
         from salaries_60 s
     ) A ;
# +------+------+--+-------------+
# |emp_no|salary|rn|running_total|
# +------+------+--+-------------+
# |1     |100   |1 |100          |
# |2     |1000  |2 |1100         |
# |3     |10000 |3 |11100        |
# |4     |500   |4 |11600        |
# |5     |5000  |5 |16600        |
# +------+------+--+-------------+

# 法二：(用分析函数)
SELECT emp_no, salary, SUM(salary) OVER (ORDER BY emp_no) AS running_total
FROM salaries
WHERE to_date = '9999-01-01';

# mysql的分析函数
# 分析函数之连续求和sum(…) over(…)
# 分析函数之排序值rank()和dense_rank()
# 分析函数之排序后顺序号row_number()
# 分析函数之取上下行数据lag()和lead()
# 分析函数和聚合函数的区别
# 普通的聚合函数用group by分组，每个分组返回一个统计值，
# 分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
# 语法
# FUNCTION_NAME(<参数>,…)
# OVER (<PARTITION BY 表达式,…>
# 	  <ORDER BY 表达式 <ASC DESC>
# 	  <NULLS FIRST NULLS LAST>>
# 	  <WINDOWING子句>)
# 分析函数带有一个开窗函数over()，包含三个分析子句:
#
# 分组(partition by)
# 排序(order by)
# 窗口(rows)
# 规则：
# sum(...) over( )，对所有行求和
#
# sum(...) over( order by ... )， 连续求和
#
#
# sum(...) over( partition by... )，同组内所行求和
#
# sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内
# 总结
# 在"… from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
# 里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了
# ① 连续求和
# 连续求和分析函数 sum(…) over(…)
# 【功能】连续求和分析函数
# 【参数】具体参示例
SELECT *
FROM salaries_60 s ;
# +------+------+----------+----------+---+
# |emp_no|salary|from_date |to_date   |sex|
# +------+------+----------+----------+---+
# |1     |100   |2021-01-23|9999-01-01|0  |
# |2     |1000  |2021-01-23|9999-01-01|1  |
# |3     |10000 |2021-01-23|9999-01-01|1  |
# |4     |500   |2021-01-23|9999-01-01|0  |
# |5     |5000  |2021-01-23|9999-01-01|1  |
# +------+------+----------+----------+---+
# 对所有行求和
SELECT s.emp_no, s.salary , sum(s.salary) over() as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so   |
# +------+------+-----+
# |1     |100   |16600|
# |2     |1000  |16600|
# |3     |10000 |16600|
# |4     |500   |16600|
# |5     |5000  |16600|
# +------+------+-----+


# 连续求和(连续的话就得加order by)
SELECT s.emp_no, s.salary , sum(s.salary) over(order by emp_no) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so   |
# +------+------+-----+
# |1     |100   |100  |
# |2     |1000  |1100 |
# |3     |10000 |11100|
# |4     |500   |11600|
# |5     |5000  |16600|
# +------+------+-----+

#  group by 后，每个组 返回一个结果
SELECT sum(s.salary)
FROM salaries_60 s group by  s.emp_no, s.salary;
# +-------------+
# |sum(s.salary)|
# +-------------+
# |100          |
# |1000         |
# |10000        |
# |500          |
# |5000         |
# +-------------+
#  group by 后，每个组 返回一个结果
set session sql_mode='';
SELECT sum(s.salary)
FROM salaries_60 s group by  s.salary;
# +-------------+
# |sum(s.salary)|
# +-------------+
# |100          |
# |1000         |
# |10000        |
# |500          |
# |5000         |
# +-------------+

SELECT sum(s.salary)
FROM salaries_60 s ;
# +-------------+
# |sum(s.salary)|
# +-------------+
# |16600        |
# +-------------+



# sum(sal) over (order by deptno,ename) 按emp_no 和 sex “连续”求总和
SELECT s.emp_no, s.salary , sum(s.salary) over(order by emp_no ,sex) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so   |
# +------+------+-----+
# |1     |100   |100  |
# |2     |1000  |1100 |
# |3     |10000 |11100|
# |4     |500   |11600|
# |5     |5000  |16600|
# +------+------+-----+

# 同组内求和
SELECT s.emp_no, s.salary , sum(s.salary) over(partition by sex ) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so   |
# +------+------+-----+
# |1     |100   |600  |
# |4     |500   |600  |
# |2     |1000  |16000|
# |3     |10000 |16000|
# |5     |5000  |16000|
# +------+------+-----+

# 同组内连续求和
# sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内
SELECT s.emp_no, s.salary , sum(s.salary) over(partition by sex order by emp_no) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so   |
# +------+------+-----+
# |1     |100   |100  |
# |4     |500   |600  |
# |2     |1000  |1000 |
# |3     |10000 |11000|
# |5     |5000  |16000|
# +------+------+-----+

# 即sex从大到小排列，sex组里各员工的薪水从高到低排列，累计和的规则不变（组内连续求和和 总体连续求和）。
SELECT
       s.emp_no,
       s.salary ,
       sum(s.salary) over(partition by sex order by salary desc) as sex_gro_sum ,
         sum(s.salary) over(order by salary desc) as all_sum
FROM salaries_60 s ;
# +------+------+-----------+-------+
# |emp_no|salary|sex_gro_sum|all_sum|
# +------+------+-----------+-------+
# |3     |10000 |10000      |10000  |
# |5     |5000  |15000      |15000  |
# |2     |1000  |16000      |16000  |
# |4     |500   |500        |16500  |
# |1     |100   |600        |16600  |
# +------+------+-----------+-------+

SELECT
       s.emp_no,
       s.salary ,
       sum(s.salary) over(partition by sex order by emp_no asc ,salary desc) as sex_gro_sum ,
         sum(s.salary) over(order by emp_no asc ,salary desc  ) as all_sum
FROM salaries_60 s ;
# +------+------+-----------+-------+
# |emp_no|salary|sex_gro_sum|all_sum|
# +------+------+-----------+-------+
# |1     |100   |100        |100    |
# |2     |1000  |1000       |1100   |
# |3     |10000 |11000      |11100  |
# |4     |500   |600        |11600  |
# |5     |5000  |16000      |16600  |
# +------+------+-----------+-------+

1.五星（重要：思路很好）

# 23 （五星）对所有员工的当前(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`));

select * from salaries_18 ;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |1     |100   |2021-01-23|9999-01-01|
# |2     |1000  |2021-01-23|9999-01-01|
# |3     |10000 |2021-01-23|9999-01-01|
# |4     |1000  |2021-01-23|9999-01-01|
# +------+------+----------+----------+
# dense_rank 函数的考察

select emp_no, salary, dense_rank() over (order by salary desc ,emp_no asc)
from salaries_18;
# +------+------+----------------------------------------------------+
# |emp_no|salary|dense_rank() over (order by salary desc ,emp_no asc)|
# +------+------+----------------------------------------------------+
# |3     |10000 |1                                                   |
# |2     |1000  |2                                                   |
# |4     |1000  |3                                                   |
# |1     |100   |4                                                   |
# +------+------+----------------------------------------------------+

select emp_no, salary, dense_rank() over (order by salary desc)
from salaries_18 order by emp_no asc;
# +------+------+----------------------------------------+
# |emp_no|salary|dense_rank() over (order by salary desc)|
# +------+------+----------------------------------------+
# |1     |100   |3                                       |
# |2     |1000  |2                                       |
# |3     |10000 |1                                       |
# |4     |1000  |2                                       |
# +------+------+----------------------------------------+

select emp_no, salary, dense_rank() over (order by salary desc)
from salaries_18 ;
# +------+------+----------------------------------------+
# |emp_no|salary|dense_rank() over (order by salary desc)|
# +------+------+----------------------------------------+
# |3     |10000 |1                                       |
# |2     |1000  |2                                       |
# |4     |1000  |2                                       |
# |1     |100   |3                                       |
# +------+------+----------------------------------------+

#
select emp_no, salary, dense_rank() over (order by salary desc ,emp_no asc)
from salaries_18;
# +------+------+----------------------------------------------------+
# |emp_no|salary|dense_rank() over (order by salary desc ,emp_no asc)|
# +------+------+----------------------------------------------------+
# |3     |10000 |1                                                   |
# |2     |1000  |2                                                   |
# |4     |1000  |3                                                   |
# |1     |100   |4                                                   |
# +------+------+----------------------------------------------------+

# 正确答案：
  select s.emp_no,
       salary,
       dense_rank() over (order by salary desc) as rk
from salaries_18 s
where to_date = '9999-01-01'
#   dense_rank()  定义了  rk 是降序的
order by rk, emp_no asc ;

# or
select emp_no, salary, dense_rank() over (order by salary desc)
from salaries_18 order by salary desc,emp_no asc;
# +------+------+----------------------------------------+
# |emp_no|salary|dense_rank() over (order by salary desc)|
# +------+------+----------------------------------------+
# |3     |10000 |1                                       |
# |2     |1000  |2                                       |
# |4     |1000  |2                                       |
# |1     |100   |3                                       |
# +------+------+----------------------------------------+


# 错误答案:
select emp_no, salary, dense_rank() over (order by salary desc ,emp_no asc)
from salaries_18 order by salary desc;
# +------+------+----------------------------------------------------+
# |emp_no|salary|dense_rank() over (order by salary desc ,emp_no asc)|
# +------+------+----------------------------------------------------+
# |3     |10000 |1                                                   |
# |2     |1000  |2                                                   |
# |4     |1000  |3                                                   |
# |1     |100   |4                                                   |
# +------+------+----------------------------------------------------+

# 思路二 ： 子查询添加一列 （重要思路：count 除了统计数据，还可以做排名处理：）
#  num 为  1，count  为 0 ；
# 实现dense_rank()和rank() 在于  是否加  distinct
select emp_no,
       salary,
       (select count(distinct salary)
        from salaries_18 s2
        where   s2.salary >= s1.salary)
from salaries_18 s1
order by s1.salary desc, s1.emp_no asc;
# 说明：
# 对于 salary 为 10000 这条数据， 过滤条件 s2.salary >= s1.salary--> s2.salary >= 10000,在 s2 找出大于等于 10000 的数据个数（这个个数叫做排名）
#对于 salary 为 1000 这条数据,过滤条件 s2.salary >= s1.salary--> s2.salary >= 1000,在 s2 找出大于等于 1000 的数据个数（这个个数叫做排名）
#  1000有两条，1000 有一条，count 为3，正确结果是 2 ，所以要去重
# 对于 salary 为 100 这条数据，过滤条件 s2.salary >= s1.salary--> s2.salary >= 100，在 s2 找出大于等于 1000 的数据个数（这个个数叫做排名）
#,100有一条， 1000有两条，1000 有一条，count 为4,正确结果为 3，，所以要去重


# 上述语句不去重之后的结果
select emp_no,
       salary,
       (select count(salary)
        from salaries_18 s2
        where   s2.salary >= s1.salary)
from salaries_18 s1
order by s1.salary desc, s1.emp_no asc;
# +------+------+-------------------------------------------------------------------------------------------+
# |emp_no|salary|(select count(salary)                                                                      |
# |      |      |from salaries_18 s2                                                                        |
# |      |      |where   s2.salary >= s1.salary)                                                            |
# +------+------+-------------------------------------------------------------------------------------------+
# |3     |10000 |1                                                                                          |
# |2     |1000  |3                                                                                          |
# |4     |1000  |3                                                                                          |
# |1     |100   |4                                                                                          |
# +------+------+-------------------------------------------------------------------------------------------+

 

1.（四星）：子查询的位置(select 后面作为一个新的列；from 后面作为表；where （子查询）> 10 作为判断条件) ；where id = （子查询）子查询获取库里数据

# 题目描述
# 28
# 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，
# 而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

CREATE TABLE IF NOT EXISTS film_28 (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

CREATE TABLE category_28  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

CREATE TABLE film_category_28  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);


INSERT INTO film_28 VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film_28 VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film_28 VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO film_28 VALUES(4,'AFFAIR PREJUDICE','A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank');
INSERT INTO film_28 VALUES(5,'AFRICAN EGG','A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico');
INSERT INTO film_28 VALUES(6,'AGENT TRUMAN','A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China');
INSERT INTO film_28 VALUES(7,'AIRPLANE SIERRA','A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat');
INSERT INTO film_28 VALUES(8,'AIRPORT POLLOCK','A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India');
INSERT INTO film_28 VALUES(9,'ALABAMA DEVIL','A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat');
INSERT INTO film_28 VALUES(10,'ALADDIN CALENDAR','A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China');

INSERT INTO category_28 VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(12,'Music','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(13,'New','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(14,'Sci-Fi','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(15,'Sports','2006-02-14 20:46:27');
INSERT INTO category_28 VALUES(16,'Travel','2006-02-14 20:46:27');

INSERT INTO film_category_28 VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(2,11,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(3,6,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(4,11,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(5,6,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(6,6,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(7,5,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(8,6,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(9,11,'2006-02-14 21:07:09');
INSERT INTO film_category_28 VALUES(10,15,'2006-02-14 21:07:09');

# 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，
# 而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部


# 思路一: 三表关联  ，该分类包含电影总数量(count(film_category.category_id))>=5 作为子查询来实现
select f.description, c.name, count(f.film_id)
from film_28 f
         inner join film_category_28 fmc on f.film_id = fmc.film_id and f.description like '%robot%'
         inner join category_28 c on fmc.category_id = c.category_id
 where (select count(*) from film_category_28 fmc2 where fmc2.category_id = c.category_id) >= 5
group by f.description, c.name ;



# or   (子查询作为 where后的条件)
select f.description, c.name, count(f.film_id)
from film_28 f,
     film_category_28 fmc,
     category_28 c
where f.film_id = fmc.film_id
  and f.description like '%robot%'
  and fmc.category_id = c.category_id
  and (select count(*) from film_category_28 fmc2 where fmc2.category_id = c.category_id) >= 5
group by f.description, c.name;
# +-----------------------------------------------------------------------------------------+-----------+----------------+
# |description                                                                              |name       |count(f.film_id)|
# +-----------------------------------------------------------------------------------------+-----------+----------------+
# |A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China|Documentary|1               |
# +-----------------------------------------------------------------------------------------+-----------+----------------+


# or
select *
from (
         select f.description,
                c.name,
                count(f.film_id),
                (select count(*) from film_category_28 fmc2 where fmc2.category_id = c.category_id) as ct
         from film_28 f,
              film_category_28 fmc,
              category_28 c
         where f.film_id = fmc.film_id
           and f.description like '%robot%'
           and fmc.category_id = c.category_id
         group by f.description, c.name ,ct
     ) A
where ct >= 5;
# +-----------------------------------------------------------------------------------------+-----------+----------------+--+
# |description                                                                              |name       |count(f.film_id)|ct|
# +-----------------------------------------------------------------------------------------+-----------+----------------+--+
# |A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China|Documentary|1               |5 |
# +-----------------------------------------------------------------------------------------+-----------+----------------+--+


#  思路二：先找出 该分类包含电影总数量(count(film_category.category_id))>=5部 的分类id集合 作为 category_id 的过滤条件
# # 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，
# # 而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
select c.name,count(c.name)  from category_28  c join film_category_28 fc
on c.category_id=fc.category_id
join film_28 f on f.film_id=fc.film_id
where f.description like '%robot%'
and fc.category_id in (SELECT category_id FROM film_category_28 GROUP BY category_id HAVING count(film_id)>=5)
GROUP BY c.name ;

1.

# 25 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
# 结果第一列给出员工的emp_no，
# 第二列给出其manager的manager_no，
# 第三列给出该员工当前的薪水emp_salary,
# 第四列给该员工对应的manager当前的薪水manager_salary
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_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`));

#  说明：题干和网友答案的意思是  dept_emp 里有 员工和经理信息
select  * from dept_emp_25;
# +------+-------+----------+----------+
# |emp_no|dept_no|from_date |to_date   |
# +------+-------+----------+----------+
# |1     |1      |2021-01-23|2021-01-23|
# |2     |1      |2021-01-23|2021-01-23|
# |3     |2      |2021-01-23|2021-01-23|
# |4     |2      |2021-01-23|2021-01-23|
# |5     |2      |2021-01-23|2021-01-23|
# +------+-------+----------+----------+
select  * from dept_manager_25;
# +-------+------+----------+----------+
# |dept_no|emp_no|from_date |to_date   |
# +-------+------+----------+----------+
# |1      |2     |2021-01-23|2021-01-23|
# |2      |5     |2021-01-23|2021-01-23|
# +-------+------+----------+----------+

select  * from salaries_25;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |1     |100   |2021-01-23|9999-01-01|
# |2     |1000  |2021-01-23|9999-01-01|
# |3     |10000 |2021-01-23|9999-01-01|
# |4     |500   |2021-01-23|9999-01-01|
# |5     |5000  |2021-01-23|9999-01-01|
# +------+------+----------+----------+

# 获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

# 思路一: 从员工表找出非经理的员工 ②子查询 增加一列比较其部门的经理salary
# 所有员工（不包括经理）的薪资
select de.emp_no, de.dept_no
from dept_emp_25 de
         inner join salaries_25 s on de.emp_no = s.emp_no
where emp_no not in (select emp_no from dept_manager_25) ;
# 子查询增加一列

select de.emp_no,
       de.dept_no,
       (
           select count(1)
           from salaries_25 s2
           where s2.emp_no = (select dm.emp_no from dept_manager_25 dm where dm.dept_no = de.dept_no)
#             当前部门下经理薪资要低于 该员工薪资
           and s2.salary < s.salary
       ) as cot
from dept_emp_25 de
         inner join salaries_25 s on de.emp_no = s.emp_no
where emp_no not in (select emp_no from dept_manager_25)
and cot > 0 ;
# 上述sql 识别不了 别名，加个套

select *
from (
         select de.emp_no,
                de.dept_no,
                (
                    select count(1)
                    from salaries_25 s2
                    where s2.emp_no = (select dm.emp_no from dept_manager_25 dm where dm.dept_no = de.dept_no)
#             当前部门下经理薪资要高于 该员工薪资
                      and s2.salary < s.salary
                ) as cot
         from dept_emp_25 de
                  inner join salaries_25 s on de.emp_no = s.emp_no
         where de.emp_no not in (select dm2.emp_no from dept_manager_25 dm2)
     ) A
where cot > 0;

# +------+-------+---+
# |emp_no|dept_no|cot|
# +------+-------+---+
# |3     |2      |1  |
# +------+-------+---+


# 思路二: 从员工表找出非经理的员工 ②exists 和部门经理薪资比较（exists取交集，not  exists 取差集 ）(结果有问题：什么原因？待定)

select de.emp_no, de.dept_no
from dept_emp_25 de
         inner join salaries_25 s on de.emp_no = s.emp_no
where de.emp_no not in (select emp_no from dept_manager_25)
  and exists(
        select count(*)
        from salaries_25 ms2
#             通过传入 基层员工的 的部门编号，从 dept_manager_25 获得部门经理的  emp_no，进而拿到 部门经理的salary--->s2
        where ms2.emp_no = (select dm.emp_no from dept_manager_25 dm where dm.dept_no = de.dept_no)
#                  当前部门下经理薪资要低于 该员工薪资
          and ms2.salary < s.salary
    );

#
# 思路三：因为要比较的两个字段不在一列（想办法弄到一列： 员工薪资表和经理薪资表关联）
# **查询员工当前工资表 emp_sal**
select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
# **查询经理当前工资表mag_sal**
 select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
# **联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
)as emp_sal
inner join(
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;

# or
select de.emp_no,dm.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary
and s2.to_date='9999-01-01'
and s1.to_date='9999-01-01';

 

1. exists 和 not exists 的区别

exists  是取得交集, not  exists 取得是差集
# 57  使用含有关键字exists查找未分配具体部门的员工的所有信息（一般）
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
select * from employees_57;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |皝        |男     |2021-01-23|
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# |4     |2021-01-23|慕容        |垂        |女     |2021-01-23|
# +------+----------+----------+---------+------+----------+

select * from dept_emp_57;
# |emp_no|dept_no|from_date |to_date   |
# +------+-------+----------+----------+
# |1     |1      |2021-01-23|2021-01-23|
# |2     |2      |2021-01-23|2021-01-23|
# +------+-------+----------+----------+

# 使用含有关键字exists查找未分配具体部门的员工的所有信息
# 分析：employees_57 的每条数据去匹配dept_emp_57  ，取得是不存在的（3,4）,not exists
select *
from employees_57
where not exists(select 1 from dept_emp_57 where employees_57.emp_no = dept_emp_57.emp_no);
# 查询结果：
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# |4     |2021-01-23|慕容        |垂        |女     |2021-01-23|
# +------+----------+----------+---------+------+----------+
# 思路二: 左连接  条件是  is  null

# 取交集
select *
from employees_57
where  exists(select 1 from dept_emp_57 where employees_57.emp_no = dept_emp_57.emp_no);
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |皝        |男     |2021-01-23|
# +------+----------+----------+---------+------+----------+

 

1. （五星）exists 的用法

# 56 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，
# 没有分配奖金的员工不显示对应的bonus类型btype和received

select * from employees_56;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |垂        |男     |2021-01-23|
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# |4     |2021-01-23|峰         |萧        |男     |2021-01-23|
# |5     |2021-01-23|远山        |萧        |男     |2021-01-23|
# +------+----------+----------+---------+------+----------+
select * from dept_emp_56;
# +------+-------+----------+----------+
# |emp_no|dept_no|from_date |to_date   |
# +------+-------+----------+----------+
# |1     |1      |2021-01-23|2021-01-23|
# |2     |1      |2021-01-23|2021-01-23|
# |3     |2      |2021-01-23|2021-01-23|
# |4     |2      |2021-01-23|2021-01-23|
# |5     |2      |2021-01-23|2021-01-23|
# +------+-------+----------+----------+

select * from emp_bonus_56;
# +------+-------------------+-----+
# |emp_no|received           |btype|
# +------+-------------------+-----+
# |1     |2010-01-01 00:00:00|1    |
# |2     |2010-01-01 00:00:00|2    |
# |3     |2010-01-01 00:00:00|3    |
# |4     |2010-01-01 00:00:00|4    |

# 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received

# 思路一： 左连接
select e.emp_no, d.dept_no, b.btype, b.received
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
         left join emp_bonus_56 b
                   on e.emp_no = b.emp_no;

# +------+-------+-----+-------------------+
# |emp_no|dept_no|btype|received           |
# +------+-------+-----+-------------------+
# |1     |1      |1    |2010-01-01 00:00:00|
# |2     |1      |2    |2010-01-01 00:00:00|
# |3     |2      |3    |2010-01-01 00:00:00|
# |4     |2      |4    |2010-01-01 00:00:00|
# |5     |2      |NULL |NULL               |
# +------+-------+-----+-------------------+

# 修改为：不显示null
select e.emp_no, d.dept_no, ifnull(b.btype,'') , ifnull(b.received,'')
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
         left join emp_bonus_56 b
                   on e.emp_no = b.emp_no;
# +------+-------+------------------+---------------------+
# |emp_no|dept_no|ifnull(b.btype,'')|ifnull(b.received,'')|
# +------+-------+------------------+---------------------+
# |1     |1      |1                 |2010-01-01 00:00:00  |
# |2     |1      |2                 |2010-01-01 00:00:00  |
# |3     |2      |3                 |2010-01-01 00:00:00  |
# |4     |2      |4                 |2010-01-01 00:00:00  |
# |5     |2      |                  |                     |
# +------+-------+------------------+---------------------+

# 思路二：交集 + 差集（not  exists）
select e.emp_no, d.dept_no, ifnull(b.btype, '') as btype, ifnull(b.received, '') as received
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
         inner join emp_bonus_56 b
                    on e.emp_no = b.emp_no

union all
select e.emp_no, d.dept_no, '' as btype, '' as received
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
where not exists(
#       count(*)   ,count(b.btype) 都是错误写法，直接 写个  1 就行
        select 1 from emp_bonus_56 b where b.emp_no = e.emp_no
    );
# +------+-------+-----+-------------------+
# |emp_no|dept_no|btype|received           |
# +------+-------+-----+-------------------+
# |1     |1      |1    |2010-01-01 00:00:00|
# |2     |1      |2    |2010-01-01 00:00:00|
# |3     |2      |3    |2010-01-01 00:00:00|
# |4     |2      |4    |2010-01-01 00:00:00|
# |5     |2      |     |                   |
# +------+-------+-----+-------------------+


# 错误sql 如下
select e.emp_no, d.dept_no, '' as btype, '' as received
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
where  exists(
#       count(*)   会将 null 的也带出来，也就是 exists 失效
        select count(*) from emp_bonus_56 b where b.emp_no = e.emp_no
    );
#  结果 是 多了 第五条
# +------+-------+-----+--------+
# |emp_no|dept_no|btype|received|
# +------+-------+-----+--------+
# |1     |1      |     |        |
# |2     |1      |     |        |
# |3     |2      |     |        |
# |4     |2      |     |        |
# |5     |2      |     |        |
# +------+-------+-----+--------+

select e.emp_no, d.dept_no, '' as btype, '' as received
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
where  exists(
#       count(*)   会将 null 的也带出来，也就是 exists 失效
        select count(b.btype) from emp_bonus_56 b where b.emp_no = e.emp_no
    );
# +------+-------+-----+--------+
# |emp_no|dept_no|btype|received|
# +------+-------+-----+--------+
# |1     |1      |     |        |
# |2     |1      |     |        |
# |3     |2      |     |        |
# |4     |2      |     |        |
# |5     |2      |     |        |
# +------+-------+-----+--------+
select e.emp_no, d.dept_no, '' as btype, '' as received
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
where  exists(
#       count(*)   会将 null 的也带出来，也就是 exists 失效
        select b.btype from emp_bonus_56 b where b.emp_no = e.emp_no
    );
# +------+-------+-----+--------+
# |emp_no|dept_no|btype|received|
# +------+-------+-----+--------+
# |1     |1      |     |        |
# |2     |1      |     |        |
# |3     |2      |     |        |
# |4     |2      |     |        |
# +------+-------+-----+--------+


select e.emp_no
from employees_56 e
where  exists(
        select 1 from emp_bonus_56 b where b.emp_no = e.emp_no
    );
# +------+
# |emp_no|
# +------+
# |1     |
# |2     |
# |3     |
# |4     |
# +------+

#  以下两个sql 说明 count（*） 是会包括 null 结果的，所以要用 count(1) or count(列名)
select e.emp_no
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
where  exists(
        select count(*) from emp_bonus_56 b where b.emp_no = e.emp_no
    );
# +------+
# |emp_no|
# +------+
# |1     |
# |2     |
# |3     |
# |4     |
# |5     |
# +------+

select e.emp_no
from employees_56 e
         inner join dept_emp_56 d on e.emp_no = d.emp_no
where  exists(
        select 1 from emp_bonus_56 b where b.emp_no = e.emp_no
    );
# +------+
# |emp_no|
# +------+
# |1     |
# |2     |
# |3     |
# |4     |
# +------+

2. 低于最大，小于最小(范围查询)

# 18 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗
CREATE TABLE `employees_18` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries_18` (
`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`));

select * from employees_18 ;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |垂        |男     |2021-01-23|
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# +------+----------+----------+---------+------+----------+
select * from  salaries_18;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |1     |100   |2021-01-23|9999-01-01|
# |2     |1000  |2021-01-23|9999-01-01|
# |3     |10000 |2021-01-23|9999-01-01|
# +------+------+----------+----------+
# 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗
# 只有三条数据的时候可以这么写（排除掉最大最小的就是中间的）
select e.emp_no, s.salary, e.last_name, e.first_name
from employees_18 e
         inner join salaries_18 s
                    on e.emp_no = s.emp_no
where salary not in (select max(s2.salary) from salaries_18 s2 union all select min(s2.salary) from salaries_18 s2);
# +------+------+---------+----------+
# |emp_no|salary|last_name|first_name|
# +------+------+---------+----------+
# |2     |1000  |垂        |慕容        |
# +------+------+---------+----------+

# 思路2 dense_rank, oby 是别名，加个套才能识别这个别名（即：用 oby = 2  过滤数据）
# rank(),rownum() 也要类似的功能

select emp_no ,salary ,last_name, first_name
from (
         select e.emp_no, s.salary, e.last_name, e.first_name, dense_rank() over (order by s.salary desc) as oby
         from employees_18 e
                  inner join salaries_18 s
                             on e.emp_no = s.emp_no
     ) A
where oby = 2;
# +------+------+---------+----------+---+
# |emp_no|salary|last_name|first_name|oby|
# +------+------+---------+----------+---+
# |2     |1000  |垂        |慕容        |2  |
# +------+------+---------+----------+---+

# 思路三  排除掉最大的最大值
with tmp_second_saray as (
      select e.emp_no, s.salary, e.last_name, e.first_name
         from employees_18 e
                  inner join salaries_18 s
                             on e.emp_no = s.emp_no
        where salary < (select max(salary) from salaries_18)
    )
select *  from tmp_second_saray where salary = (select  max(salary) from tmp_second_saray) ;
# +------+------+---------+----------+
# |emp_no|salary|last_name|first_name|
# +------+------+---------+----------+
# |2     |1000  |垂        |慕容        |
# +------+------+---------+----------+

# 思路三的另一种写法
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
select e.emp_no, s.salary, e.last_name, e.first_name,max(s.salary)
         from employees_18 e
                  inner join salaries_18 s
                             on e.emp_no = s.emp_no
#    过滤掉最大的后，老二就是最大的
        where salary < (select max(salary) from salaries_18);
# +------+------+---------+----------+-------------+
# |emp_no|salary|last_name|first_name|max(s.salary)|
# +------+------+---------+----------+-------------+
# |1     |100   |一光       |郭         |1000         |
# +------+------+---------+----------+-------------+

 

3.

# 24 (一般) 获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager_24` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`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`));

select * from employees_24 ;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |垂        |男     |2021-01-23|
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# |4     |2021-01-23|峰         |萧        |男     |2021-01-23|
# |5     |2021-01-23|远山        |萧        |男     |2021-01-23|
# +------+----------+----------+---------+------+----------+
select * from dept_emp_24 ;
# +------+-------+----------+----------+
# |emp_no|dept_no|from_date |to_date   |
# +------+-------+----------+----------+
# |1     |1      |2021-01-23|2021-01-23|
# |2     |2      |2021-01-23|2021-01-23|
# +------+-------+----------+----------+
select * from dept_manager_24 ;
# +-------+------+----------+----------+
# |dept_no|emp_no|from_date |to_date   |
# +-------+------+----------+----------+
# |1      |4     |2021-01-23|2021-01-23|
# |2      |5     |2021-01-23|2021-01-23|
# +-------+------+----------+----------+
select * from salaries_24 ;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |1     |100   |2021-01-23|9999-01-01|
# |2     |1000  |2021-01-23|9999-01-01|
# |3     |10000 |2021-01-23|9999-01-01|
# |4     |1000  |2021-01-23|9999-01-01|
# +------+------+----------+----------+

#  （这个不是标准答案）获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
#  给了很多无用表
select de.dept_no ,de.emp_no ,s.salary from  dept_emp_24  de inner join salaries_24  s on de.emp_no = s.emp_no;

# 标准答案（这个思路是 employees既有员工又有经理 ）
SELECT de.dept_no,de.emp_no,s.salary
FROM dept_emp de
JOIN employees e
ON de.emp_no=e.emp_no
JOIN salaries s
ON de.emp_no=s.emp_no
WHERE de.emp_no NOT IN(
    SELECT emp_no
    FROM dept_manager_24
)
AND s.to_date='9999-01-01';

 

1. case when then end 的用法


# 59 给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
# bonus类型btype为1其奖金为薪水salary的10%，
# btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
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`));


select * from employees_59 ;
# +------+----------+----------+---------+------+----------+
# |emp_no|birth_date|first_name|last_name|gender|hire_date |
# +------+----------+----------+---------+------+----------+
# |1     |2021-01-23|郭         |一光       |男     |2021-01-23|
# |2     |2021-01-23|慕容        |垂        |男     |2021-01-23|
# |3     |2021-01-23|慕容        |雪        |女     |2021-01-23|
# |4     |2021-01-23|峰         |萧        |男     |2021-01-23|
# |5     |2021-01-23|远山        |萧        |男     |2021-01-23|
# +------+----------+----------+---------+------+----------+
select * from dept_emp_59 ;
# +------+-------+----------+----------+
# |emp_no|dept_no|from_date |to_date   |
# +------+-------+----------+----------+
# |1     |1      |2021-01-23|2021-01-23|
# |2     |1      |2021-01-23|2021-01-23|
# |3     |2      |2021-01-23|2021-01-23|
# |4     |2      |2021-01-23|2021-01-23|
# |5     |2      |2021-01-23|2021-01-23|
# +------+-------+----------+----------+
select * from salaries_59 ;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |1     |100   |2021-01-23|9999-01-01|
# |2     |1000  |2021-01-23|9999-01-01|
# |3     |10000 |2021-01-23|9999-01-01|
# |4     |500   |2021-01-23|9999-01-01|
# |5     |5000  |2021-01-23|9999-01-01|
# +------+------+----------+----------+
select * from emp_bonus_59 ;
# +------+-------------------+-----+
# |emp_no|received           |btype|
# +------+-------------------+-----+
# |1     |2021-01-23 00:00:00|1    |
# |2     |2021-01-23 00:00:00|2    |
# |3     |2021-01-23 00:00:00|3    |
# +------+-------------------+-----+



# 给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
# bonus类型btype为1其奖金为薪水salary的10%，
# btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
# 思路一：  case when then
select e.emp_no, e.first_name, e.last_name, s.salary, b.btype,
       case b.btype  when b.btype=1   then s.salary*1.1
           when b.btype=2   then s.salary*1.2
           else   s.salary*1.3
           end as bs
from employees_59 e
         left join salaries_59 s on e.emp_no = s.emp_no
         left join emp_bonus_59 b on e.emp_no = b.emp_no ;

# or  (省去括号里的条件)

select e.emp_no, e.first_name, e.last_name, s.salary, b.btype,
       case b.btype  when 1   then s.salary*1.1
           when 2   then s.salary*1.2
           else   s.salary*1.3
           end as bs
from employees_59 e
         left join salaries_59 s on e.emp_no = s.emp_no
         left join emp_bonus_59 b on e.emp_no = b.emp_no ;
# +------+----------+---------+------+-----+-------+
# |emp_no|first_name|last_name|salary|btype|bs     |
# +------+----------+---------+------+-----+-------+
# |1     |郭         |一光       |100   |1    |110.0  |
# |2     |慕容        |垂        |1000  |2    |1200.0 |
# |3     |慕容        |雪        |10000 |3    |13000.0|
# |4     |峰         |萧        |500   |NULL |650.0  |
# |5     |远山        |萧        |5000  |NULL |6500.0 |
# +------+----------+---------+------+-----+-------+

# 参考答案：(这个参考答案有问题，没有列出全部数据来)
select e.emp_no,e.first_name,e.last_name,eb.btype,sa.salary,
(case eb.btype
    when 1 then sa.salary*0.1
    when 2 then sa.salary*0.2
    else sa.salary*0.3 end) as bonus
from employees_59 e inner join emp_bonus_59 eb on e.emp_no=eb.emp_no
inner join salaries_59 sa on e.emp_no =sa.emp_no
where sa.to_date='9999-01-01' ;
# +------+----------+---------+-----+------+------+
# |emp_no|first_name|last_name|btype|salary|bonus |
# +------+----------+---------+-----+------+------+
# |1     |郭         |一光       |1    |100   |10.0  |
# |2     |慕容        |垂        |2    |1000  |200.0 |
# |3     |慕容        |雪        |3    |10000 |3000.0|
# +------+----------+---------+-----+------+------+
# 牛客


# 69 需求 描述
# 请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序
#  涉及到三张表  login_69 user_69 client_69
select * from login_69 ;
# +--+-------+---------+----------+
# |id|user_id|client_id|date      |
# +--+-------+---------+----------+
# |1 |2      |1        |2021-02-12|
# |2 |3      |2        |2021-02-12|
# |3 |2      |2        |2021-02-13|
# |4 |3      |2        |2021-02-13|
# +--+-------+---------+----------+

select * from user_69 ;
# +--+--------+
# |id|name    |
# +--+--------+
# |1 |tm      |
# |2 |fh      |
# |3 |wangchao|
# +--+--------+

select * from client_69 ;
# +--+-------+
# |id|name   |
# +--+-------+
# |1 |pc     |
# |2 |ios    |
# |3 |android|
# |4 |h5     |
# +--+-------+

# 标准答案  1
# 三表直接关联可以获取所有登录记录，最后根据 user_id 分组，分组后获取 最大的登录日期就是符合要求的数据
select t2.name as u_n,t3.name as c_n,max(t1.date)as d
from login t1  join user t2 on t1.user_id=t2.id
join client t3 on t1.client_id =t3.id
group by t1.user_id
order by t2.name ;

#  标准答案 2
# having 分组后，作为组内数据筛选

select u.name,c.name,l.date
from login l
inner join user u
on l.user_id = u.id
inner join client c
on l.client_id=c.id
group by l.user_id
having l.date = max(l.date)
order by u.name asc;

# 思路 ① select 后的 子查询  login_69 ；user_69 ； client_69 ；
# 请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

select l.*,
       u.name ,
       (select max(date) from login_69 l2 where l2.user_id = u.id)

from login_69 l
         inner join user_69 u on l.user_id = u.id;

# +--+-------+---------+----------+--------+-----------------------------------------------------------+
# |id|user_id|client_id|date      |name    |(select max(date) from login_69 l2 where l2.user_id = u.id)|
# +--+-------+---------+----------+--------+-----------------------------------------------------------+
# |1 |2      |1        |2021-02-12|fh      |2021-02-13                                                 |
# |2 |3      |2        |2021-02-12|wangchao|2021-02-13                                                 |
# |3 |2      |2        |2021-02-13|fh      |2021-02-13                                                 |
# |4 |3      |2        |2021-02-13|wangchao|2021-02-13                                                 |
# +--+-------+---------+----------+--------+-----------------------------------------------------------+

select
       u.name,
       (select max(date) from login_69 l2 where l2.user_id = u.id) as dat,
       (select distinct c.name
        from login_69 l3
                 inner join client_69 c on l3.client_id = c.id
#        解决子查询返回多条数据
        where l3.user_id = u.id limit 1) as name
from login_69 l
         inner join user_69 u on l.user_id = u.id;
# +--------+----------+----+
# |name    |dat       |name|
# +--------+----------+----+
# |fh      |2021-02-13|pc  |
# |wangchao|2021-02-13|ios |
# |fh      |2021-02-13|pc  |
# |wangchao|2021-02-13|ios |
# +--------+----------+----+
# 去重
select
       u.name as uName,
       (select max(date) from login_69 l2 where l2.user_id = u.id) as dat,
       (select distinct c.name
        from login_69 l3
                 inner join client_69 c on l3.client_id = c.id
#        解决子查询返回多条数据
        where l3.user_id = u.id limit 1) as name
from login_69 l
         inner join user_69 u on l.user_id = u.id group by uName ;

# +--------+----------+----+
# |uName   |dat       |name|
# +--------+----------+----+
# |fh      |2021-02-13|pc  |
# |wangchao|2021-02-13|ios |
# +--------+----------+----+

# 测试 分组
select
       u.name as uName,
       (select max(date) from login_69 l2 where l2.user_id = u.id) as dat,
       (select distinct c.name
        from login_69 l3
                 inner join client_69 c on l3.client_id = c.id
#        解决子查询返回多条数据
        where l3.user_id = u.id limit 1) as name
from login_69 l
         inner join user_69 u on l.user_id = u.id group by uName ,name;
# +--------+----------+----+
# |uName   |dat       |name|
# +--------+----------+----+
# |fh      |2021-02-13|pc  |
# |wangchao|2021-02-13|ios |
# +--------+----------+----+

# 思路②  select 子查询，直接追加 每个user最近的时间

select u.name as uName,
       c.name as cName    ,
      (select max(date) from login_69 l3 where l3.user_id = l.user_id  group by l3.user_id) as lastDate
from user_69 u
         inner join login_69 l on u.id = l.user_id
         inner join client_69 c on c.id = l.client_id  group by  uName;
# +--------+-----+----------+
# |uName   |cName|lastDate  |
# +--------+-----+----------+
# |fh      |pc   |2021-02-13|
# |wangchao|ios  |2021-02-13|
# +--------+-----+----------+

# 思路 ② exists
# 请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序
# login_69 ；user_69 ； client_69 ；

select u.name as uName,
       c.name as cName,
       l.date as lastDate
from user_69 u
         inner join login_69 l on u.id = l.user_id
         inner join client_69 c on c.id = l.client_id
where exists(select 1
             from login_69 as l2
             where l2.user_id = l.user_id
#                获取每个用户最近登录日期（最大的登录日期）
               and l.date = (select max(date) from login_69 l3 where l3.user_id = l.user_id group by l3.user_id));
# +--------+-----+----------+
# |uName   |cName|lastDate  |
# +--------+-----+----------+
# |fh      |ios  |2021-02-13|
# |wangchao|ios  |2021-02-13|
# +--------+-----+----------+


# 68
# 需求：牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数
select * from login_69;
# +--+-------+---------+----------+
# |id|user_id|client_id|date      |
# +--+-------+---------+----------+
# |1 |2      |1        |2021-02-11|
# |2 |3      |2        |2021-02-12|
# |3 |2      |2        |2021-02-13|
# |4 |3      |2        |2021-02-13|
# |5 |4      |2        |2021-02-14|
# |6 |4      |2        |2021-02-15|
# |7 |5      |2        |2021-02-16|
# |8 |5      |2        |2021-02-17|
# |9 |6      |2        |2021-02-18|
# |10|6      |2        |2021-02-18|
# |11|7      |2        |2021-02-19|
# +--+-------+---------+----------+

# 需求：牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数
# 思路①  当天新用户，时间小于 当天是没有数据的，所以就找当天的新数据

select date,
       (select count(*) from login_69 where date < ll.date   ) as A,
       (select count(*) from login_69 where date <= ll.date) as B,
      ( select (B - A)) as result
from login_69 ll
group by date;

# 加个套
select date,(B - A) as result from (
   select date,
       (select count(*) from login_69 where date < ll.date   ) as A,
       (select count(*) from login_69 where date <= ll.date) as B
from login_69 ll
group by date
                  ) As AA;
# +----------+------+
# |date      |result|
# +----------+------+
# |2021-02-11|1     |
# |2021-02-12|1     |
# |2021-02-13|2     |
# |2021-02-14|1     |
# |2021-02-15|1     |
# |2021-02-16|1     |
# |2021-02-17|1     |
# |2021-02-18|2     |
# |2021-02-19|1     |
# +----------+------+
# 灵感来源  从 a,b,c,d 中找到有多少个 逗号
# select length('a,b,c,d')  - ( select length(replace('a,b,c,d',',',''))) ;
# +----------------------------------------------------------------+
# |length('a,b,c,d')  - ( select length(replace('a,b,c,d',',','')))|
# +----------------------------------------------------------------+
# |3                                                               |
# +----------------------------------------------------------------+
# 推荐SQL：

select a.date,
sum(case
    when rank=1
    then 1
    else 0
    end) new
from (select date, row_number()
 over(partition by user_id order by date) as rank
      from login_69) as a
group by date;

 

 

guoyiguang2
关注
  • 2
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
几道比较难的SQL题
07-16 632
上条记录和下一条记录 在展示博客文章时，在文章底部需要展示上一篇文章和下一篇文章，文章的排序当然是按照时间排序的。 选定下一条时可以用limit 1来实现，选取上一条时可以倒序limit 1实现 (SELECT * FROM article WHERE create_time<now_article_time ORDER BY create_time DESC LIMIT 1) U...
牛客网-SQL篇-全部试题-较难-SQL74 考试分数(三)
qq_45491551的博客
04-21 536
SQL 问题 每个岗位分数排名前2名的用户 解题 1.看到排名，想到窗口函数：rank()、dense_rank()、row_number()。本题中可能出现分数相同的情况，此时排名也相同，因此选用dense_rank()函数（rank()函数排列序号不连续、row_number()函数不考虑分数相同时排名并列情况）。先查看排序后的临时表，SQL代码如下： select g.id, l.name, g.score, dense_rank() over(partition by l.name or
牛客网【SQL】经典难题
qq_65184431的博客
08-04 615
sql难题
牛客网——SQL21[感觉这题好难啊] &&SQL22
xw_009的博客
08-11 315
牛客网——SQL21记录 题目描述： 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序 （注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面） CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_
一些比较难的SQL语句查询
菩提本无树 明镜亦非台 本来无一物 何处惹尘埃
10-15 3643
查询各科成绩都高于90分的人。 查询每个部门薪水最高的人。 有两张表employees、departments; employees +------+------+---------------+--------+ | id | name | department_id | salary | +------+------+---------------+--------+ | 2
牛客网之各种数据库SQL项目实战+代码
最新发布
05-07
牛客网之各种数据库SQL项目实战+代码. 牛客网之各种数据库SQL项目实战+代码. 牛客网之各种数据库SQL项目实战+代码. 牛客网之各种数据库SQL项目实战+代码. 牛客网之各种数据库SQL项目实战+代码. 牛客网之各种...
SQL语句大全(经典珍藏版)
10-22
这份“SQL语句大全(经典珍藏版)”显然是一个全面介绍SQL语言的学习资料，适合初学者逐步提升对数据库管理的理解。 在SQL中，主要分为四大类语句：SELECT、INSERT、UPDATE和DELETE，它们分别对应于数据的查询、插入...
SQL入门经典（第5版）.pdf
11-21
SQL入门经典（第5版）.pdf
SQL入门经典
09-24
Beginning SQL清华大学出版的那边红皮书，从基础到高级，把SQL介绍的很详细。
Sql 经典作品，Joe Celko 大牛
05-27
1.SQL.Puzzles.and.Answers.2nd.Edition.Sep.2006 2.Morgan.Kaufmann.Joe.Celkos.SQL.Programming.Style.Apr.2005.eBook-DDU 3.Joe Celko’s Analytics and OLAP in SQL 4.Joe Celko’s _Data_and_Databases_-_...
精妙的sql学习语句很难得的啊
10-16
精妙的sql学习语句很难得的啊,大家共同进步
SQL练习题和答案(难度★★★☆☆)
03-06
第二天 1、查询客户表，统计每个机构2000年之前开户数、2000~2005开户数（含头不含尾）、2005~2010开户数（含头不含尾）、2010之后开户数 展示字段：机构号、2000年之前开户数、2000~2005年开户数、2005~2010年开户数、2010年之后开户数 2、查询客户表，按年份统计，每年、每个机构开户数占全年开户数的占比 展示字段：年份、机构号、开户数、开户占比百分比（百分比） 3、统计所有客户的客户号、存款账户数、2011.12.31日的存款余额、2011.12存款月日均、贷款账户数、2011.12.31日的贷款余额、2011.12贷款月日均 备注：null置为0 第三天 1、统计所有客户的2011.12.31日的存款余额、存款比上日余额、存款比上月余额、、存款比上年余额 备注：存款比上日余额 = 2011.12.31日的存款余额-2011.12.30日的存款余额 存款比上月余额 = 2011.12.31日的存款余额-2011.11.30日的存款余额 存款比上年余额 = 2011.12.31日的存款余额-2010.12.31日的存款余额 只有2011.12.31这个日期可以写死，其他日期要通过2011.12.31这个日期来生成。 2、统计所有2011年存款年日均大于100的客户号、客户名称、存款账户数、2011年年日均 第四天 1、统计所有2011年存款年日均和2011年贷款年日均都大于100的客户号、存款账户数、2011年存款年日均、贷款账户数、2011年贷款年日均 2、统计所有客户的客户号、 存款标志（有存款账户的客户置为1、没存款账户的客户置为0）、 贷款标志（有贷款借据的客户置为1、没贷款借据的客户置为0）、 存款质量分类（2011年存款年日均>=10000置为优质、2011年存款年日均>=1000<10000 置为良好、2011年存款年日均=10000置为优质、2011年贷款年日均>=1000<10000 置为良好、2011年贷款年日均<1000置为普通）、 2011年贷款年日均 第五天 1、根据《事件表.xlsx》来建表，然后将excel中的数据导入到目标表中 2、根据客户表、存款信息表、事件表，统计每个客户2017年的客户号、 交易账户数（客户下有多少个账户有交易就是多少）、 当年有交易的天数（如果2017年有5天有过交易，则有交易天数为5）、 当年有交易总月数（如果2017的1、3、5月有交易，则有交易总月数为3）、 最大的月交易总金额（按月统计交易金额，存放最大的月交易金额）、 最大月交易金额的月份（按月统计交易金额，存放交易金额最大的月份）、 年总交易金额、 年交易金额排名（按客户排名，如果总交易金额为0，则不参与排名，排名置为9999）、 年总手续费、 年总手续费排名（按客户排名，如果总手续费为0，则不参与排名，排名置为9999）
牛客网SQL在线编程答案（困难部分）
qq_37834723的博客
08-04 233
两天时间抽空做了10个困难级别的题目，答案在这里留一下。 #牛客12 select * from dept_emp; select * from salaries; select m.dept_no,m.emp_no,m.salary maxSalary from (select rank() over(partition by a.dept_no ORDER BY b.salary DESC) dd,a.dept_no,a.emp_no,b.salary from dept_emp a,sala
一些比较复杂的SQL题目
银的博客
10-25 3410
1.计算次日、三日、七日留存 2. 计算最大连续登陆天数 3.每个班平均分大于80分的人数及占比 4.topN问题 4——5题表格 5. 找出2017年入学，专业为计算机的年龄最小的10位同学 select name, id, class from( select name, id, class, rank() over(order by age) as ranking from tb_student where in_time = 2017 and major = '计算机') as t where r
SQL 经典50题（题目+解答）（1）
热门推荐
Robin_Pi的博客
03-10 1万+
SQL 经典50题（题目+解答）（1）
SQL不同难度的网站和题目总结
smile071008的博客
06-11 535
SQL 菜鸟,这里给出的都是我做过的题,仅供参考. 1.简单入门级 https://sqlbolt.com/ 课程比较少,没有弯弯绕,题目比较直接易解.缺点是网页比较慢. https://sqlzoo.net/wiki/SELECT_basics/zh 每道题都是在给出的代码中直接修改,不用自己从头写.我没学完,感觉课程比前一个丰富,有创建视图等. https://www.w3reso...
常用经典SQL语句大全.doc
07-01
常用经典SQL语句大全.doc是一个包含常用SQL语句的文档，该文档包括了数据库的创建和删除、备份和恢复数据、创建和删除表、增加和删除列、添加和删除主键、创建和删除索引、创建和删除视图等基础操作。该文档还提供了...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值