牛客-sql(困难难度)-2022-03-03

guoyiguang2
已于 2022-03-14 15:30:36 修改
阅读量385
点赞数
分类专栏： mysql 文章标签： sql 数据库 database
于 2022-03-03 12:37:29 首次发布
本文链接：https://blog.csdn.net/qq_38807606/article/details/123250825
版权
mysql 专栏收录该内容
33 篇文章 0 订阅
订阅专栏
SQL91 获得积分最多的人(三):(个人难度: 三星)
推荐思路儿解法(高赞解答)
drop table if exists user;
drop table if exists grade_info;

CREATE TABLE user (
id  int(4) NOT NULL,
name varchar(32) NOT NULL
);

CREATE TABLE grade_info (
user_id  int(4) NOT NULL,
grade_num int(4) NOT NULL,
type varchar(32) NOT NULL
);

INSERT INTO user VALUES
(1,'tm'),
(2,'wwy'),
(3,'zk'),
(4,'qq'),
(5,'lm');

INSERT INTO grade_info VALUES
(1,3,'add'),
(2,3,'add'),
(1,1,'reduce'),
(3,3,'add'),
(4,3,'add'),
(5,3,'add'),
(3,1,'reduce');


# 需求：
# 请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序

select * from user ;

# +--+----+
# |id|name|
# +--+----+
# |1 |tm  |
# |2 |wwy |
# |3 |zk  |
# |4 |qq  |
# |5 |lm  |
# +--+----+

select * from grade_info ;

# +-------+---------+------+
# |user_id|grade_num|type  |
# +-------+---------+------+
# |1      |3        |add   |
# |2      |3        |add   |
# |1      |1        |reduce|
# |3      |3        |add   |
# |4      |3        |add   |
# |5      |3        |add   |
# |3      |1        |reduce|
# +-------+---------+------+

#  grade_info 说明：  第一条数据 说明 id 为  1 的 用户 增加了 3个积分；第三条数据 说明  id 为 1 的用户减少了 1个积分

# ① 每个员工的增加和减少的积分

  select user_id,type  from grade_info group by user_id, type  order  by  user_id ;

    # +-------+------+
    # |user_id|type  |
    # +-------+------+
    # |1      |add   |
    # |1      |reduce|
    # |2      |add   |
    # |3      |add   |
    # |3      |reduce|
    # |4      |add   |
    # |5      |add   |
    # +-------+------+



  #  每个用户  add 和 reduce 的分数
  select user_id,type ,sum(grade_num) as  total0 from grade_info group by user_id, type  order  by  user_id ;

    # +-------+------+------+
    # |user_id|type  |total0|
    # +-------+------+------+
    # |1      |add   |3     |
    # |1      |reduce|1     |
    # |2      |add   |3     |
    # |3      |add   |3     |
    # |3      |reduce|1     |
    # |4      |add   |3     |
    # |5      |add   |3     |
    # +-------+------+------+

 #  每个用户  add 和 reduce 的分数（reduce 的话是负数）


 select user_id,type ,case type when 'add'  then sum(grade_num) else -sum(grade_num)   end as  total0 from grade_info group by user_id, type  order  by  user_id ;

    # +-------+------+------+
    # |user_id|type  |total0|
    # +-------+------+------+
    # |1      |add   |3     |
    # |1      |reduce|-1    |
    # |2      |add   |3     |
    # |3      |add   |3     |
    # |3      |reduce|-1    |
    # |4      |add   |3     |
    # |5      |add   |3     |
    # +-------+------+------+

#  最终结果求和

select user_id,name , sum(total0)
from (
         select user_id, type,  case type when 'add' then sum(grade_num) else -sum(grade_num) end as total0 ,user.name
         from grade_info inner join user on grade_info.user_id = user.id
         group by user_id, type ,name
         order by user_id
     ) as Tem
group by user_id,name ;

    #    +-------+----+-----------+
    # |user_id|name|sum(total0)|
    # +-------+----+-----------+
    # |1      |tm  |2          |
    # |2      |wwy |3          |
    # |3      |zk  |2          |
    # |4      |qq  |3          |
    # |5      |lm  |3          |
    # +-------+----+-----------+


# 需求：
# 请你写一个SQL查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序





#  用 rank（1,1,2） 和 dense_rank(1,1,3  排名是不连续的) 排序取第一条数据
select   dense_rank() over (order by total asc )   rank0  ,user_id,name , total  from  ( select user_id,name , sum(total0) as  total
from (
         select user_id, type,  case type when 'add' then sum(grade_num) else -sum(grade_num) end as total0 ,user.name
         from grade_info inner join user on grade_info.user_id = user.id
         group by user_id, type ,name
         order by user_id
     ) as Tem  group by user_id,name) as Tem  ;

    # +-----+-------+----+-----+
    # |rank0|user_id|name|total|
    # +-----+-------+----+-----+
    # |1    |1      |tm  |2    |
    # |1    |3      |zk  |2    |
    # |2    |2      |wwy |3    |
    # |2    |4      |qq  |3    |
    # |2    |5      |lm  |3    |
    # +-----+-------+----+-----+


# 取最大的


select user_id,name , total  from (select   dense_rank() over (order by total desc )   rank0  ,user_id,name , total  from  ( select user_id,name , sum(total0) as  total
from (
         select user_id, type,  case type when 'add' then sum(grade_num) else -sum(grade_num) end as total0 ,user.name
         from grade_info inner join user on grade_info.user_id = user.id
         group by user_id, type ,name

     ) as Tem  group by user_id,name) as Tem ) as Tem2  where  rank0 = 1;

# 思路二：（推荐做法：时间复杂度和空间复杂度都低） 还是窗口函数：① 从 grade_info 获取每个user_id 的总分数，并用窗口函数排序 ② 关联出姓名等信息


# ①
select user_id,  if(type = 'add',grade_info.grade_num,-grade_info.grade_num) as addReduceNum
         from grade_info    ;

# +-------+------------+
# |user_id|addReduceNum|
# +-------+------------+
# |1      |3           |
# |2      |3           |
# |1      |-1          |
# |3      |3           |
# |4      |3           |
# |5      |3           |
# |3      |-1          |
# +-------+------------+

select user_id ,sum(addReduceNum)
from (select user_id, if(type = 'add', grade_info.grade_num, -grade_info.grade_num) as addReduceNum
      from grade_info) as Tem group by user_id;

# +-------+-----------------+
# |user_id|sum(addReduceNum)|
# +-------+-----------------+
# |1      |2                |
# |2      |3                |
# |3      |2                |
# |4      |3                |
# |5      |3                |
# +-------+-----------------+

select user_id  , total ,rank() over (order by total desc ) as rko
from (select user_id, sum(if(type = 'add', grade_info.grade_num, -grade_info.grade_num)) as total
      from grade_info group by user_id) as Tem group by user_id;

#  取出 rko 为 1  的就是  分数最高的

select user_id, (select name from user where user.id = user_id ) as name ,total  from (
select user_id  , total ,rank() over (order by total desc ) as rko
from (select user_id, sum(if(type = 'add', grade_info.grade_num, -grade_info.grade_num)) as total
      from grade_info group by user_id) as Tem group by user_id) as Tem1 where rko = 1  ;


# +-------+----+-----+
# |user_id|name|total|
# +-------+----+-----+
# |2      |wwy |3    |
# |4      |qq  |3    |
# |5      |lm  |3    |
# +-------+----+-----+

============================================================================



# SQL12 获取每个部门中当前员工薪水最高的相关（ps：窗口函数解决每个部门内最高数据问题：窗口函数 可以先分组，分组之后可以 组内的内部 排序，获取序号，序号为  1 就是最大的）
# eg ： rank() over (partition by dept_emp.dept_no order by salaries.salary  desc)
drop table if exists  `dept_emp` ;
drop table if exists  `salaries` ;
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 `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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');

# 需求：
# 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:
# （注意: Mysql与Sqlite select 非聚合列的结果可能不一样)

#  dept_no    emp_no     maxSalary
#
#  d001	   10001        88958
#
#  d002	   10003        92527



select * from dept_emp ;

# +------+-------+----------+----------+
# |emp_no|dept_no|from_date |to_date   |
# +------+-------+----------+----------+
# |10001 |d001   |1986-06-26|9999-01-01|
# |10002 |d001   |1996-08-03|9999-01-01|
# |10003 |d002   |1996-08-03|9999-01-01|
# +------+-------+----------+----------+

select  * from salaries;

# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date   |
# +------+------+----------+----------+
# |10001 |88958 |2002-06-22|9999-01-01|
# |10002 |72527 |2001-08-02|9999-01-01|
# |10003 |92527 |2001-08-02|9999-01-01|
# +------+------+----------+----------+

# 思路①： 工资表和员工表关联出部门信息，然后根据部门 分区 ，内部排序 取最大的

    select dept_emp.dept_no, dept_emp.emp_no, salaries.salary
    from dept_emp
             inner join salaries on dept_emp.emp_no = salaries.emp_no ;

    #   +-------+------+------+
    # |dept_no|emp_no|salary|
    # +-------+------+------+
    # |d001   |10001 |88958 |
    # |d001   |10002 |72527 |
    # |d002   |10003 |92527 |
    # +-------+------+------+



#    分区 之后 排序

    select row0, dept_no  ,emp_no ,salary  from (select dept_emp.dept_no, dept_emp.emp_no, salaries.salary ,rank() over (partition by dept_emp.dept_no order by salaries.salary  desc) as row0
    from dept_emp
             inner join salaries on dept_emp.emp_no = salaries.emp_no) as Tem ;
    # +----+-------+------+------+
    # |row0|dept_no|emp_no|salary|
    # +----+-------+------+------+
    # |1   |d001   |10001 |88958 |
    # |2   |d001   |10002 |72527 |
    # |1   |d002   |10003 |92527 |
    # +----+-------+------+------+



# 需求：
# 获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列
#   最后的结果为：
     select dept_no , emp_no ,salary from (select row0, dept_no  ,emp_no ,salary  from (select dept_emp.dept_no, dept_emp.emp_no, salaries.salary ,rank() over (partition by dept_emp.dept_no order by salaries.salary  desc) as row0
    from dept_emp
             inner join salaries on dept_emp.emp_no = salaries.emp_no) as Tem) as Tem where  row0  = 1 order by  dept_no asc;

    # +-------+------+------+
    # |dept_no|emp_no|salary|
    # +-------+------+------+
    # |d001   |10001 |88958 |
    # |d002   |10003 |92527 |
    # +-------+------+------+

#  思路二： ① dept_emp 分组获取 部门id ② 子查询获取 这个部门的最大薪资和员工信息

 select dept_emp.dept_no, dept_emp.emp_no, salaries.salary
    from dept_emp
             inner join salaries on dept_emp.emp_no = salaries.emp_no ;

    # +-------+------+------+
    # |dept_no|emp_no|salary|
    # +-------+------+------+
    # |d001   |10001 |88958 |
    # |d001   |10002 |72527 |
    # |d002   |10003 |92527 |
    # +-------+------+------+

    select dept_no,
           (select max(salary)
            from salaries
                     inner join dept_emp on salaries.emp_no = dept_emp.emp_no
            where dept_emp.dept_no = Tem.dept_no limit 1) as salary
    from (select dept_no from dept_emp group by dept_no) as Tem ;

    # +-------+------+
    # |dept_no|salary|
    # +-------+------+
    # |d001   |88958 |
    # |d002   |92527 |
    # +-------+------+

#  然后获取员工编号（可以考虑子查询，也可以考虑  inner join 链接）


   select  Tem1.dept_no ,dept_emp.emp_no ,  Tem1.salary  from ( select dept_no,
           (select max(salary)
            from salaries
                     inner join dept_emp on salaries.emp_no = dept_emp.emp_no
            where dept_emp.dept_no = Tem.dept_no limit 1) as salary
    from (select dept_no from dept_emp group by dept_no) as Tem) as  Tem1
 # 关联 薪资表     获取 emp_no
   inner  join  salaries  on Tem1.salary = salaries.salary


# 关联 用户表
   inner  join  dept_emp  on salaries.emp_no = dept_emp.emp_no ;

    # +-------+------+------+
    # |dept_no|emp_no|salary|
    # +-------+------+------+
    # |d001   |10001 |88958 |
    # |d002   |10003 |92527 |
    # +-------+------+------+


#  思路三：（这个思路不错） 从 薪水表获取 每个部门的最大薪水

#    下面的写法

    select uni.dept_no, uni.emp_no, max_salary.salary
    from
        (select d.dept_no, s.emp_no, s.salary
         from dept_emp d join salaries s
         on d.emp_no = s.emp_no
         and d.to_date = '9999-01-01'
         and s.to_date = '9999-01-01'
        ) as uni, /* 部门编号,员工编号,当前薪水 */
        (select d.dept_no, max(s.salary) as salary
         from dept_emp d join salaries s
         on d.emp_no = s.emp_no
         and d.to_date = '9999-01-01'
         and s.to_date = '9999-01-01'
         group by d.dept_no
        ) as max_salary /* 部门编号,当前最高薪水 */
    where uni.salary = max_salary.salary
    and uni.dept_no = max_salary.dept_no
    order by uni.dept_no;

    # +-------+------+------+
    # |dept_no|emp_no|salary|
    # +-------+------+------+
    # |d001   |10001 |88958 |
    # |d002   |10003 |92527 |
    # +-------+------+------+


===============================================================================
难度：四星






select * from  order_info ;

# +--+---------+------------+------------+---------+----------+
# |id|user_id  |product_name|status      |client_id|date      |
# +--+---------+------------+------------+---------+----------+
# |1 |557336   |C++         |no_completed|1        |2025-10-10|
# |2 |230173543|Python      |completed   |2        |2025-10-12|
# |3 |57       |JS          |completed   |3        |2025-10-23|
# |4 |57       |C++         |completed   |3        |2025-10-23|
# |5 |557336   |Java        |completed   |1        |2025-10-23|
# |6 |57       |Java        |completed   |1        |2025-10-24|
# |7 |557336   |C++         |completed   |1        |2025-10-25|
# |8 |557336   |Python      |completed   |1        |2025-10-25|
# +--+---------+------------+------------+---------+----------+

# 请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，
# 那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以
# 及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，
# 并且输出结果按照user_id升序排序，以上例子查询结果如下:
#
# user_id	   first_buy_date	second_buy_date	cnt
# 57好	       2025-10-23	      2025-10-24	2
# 557336	   2025-10-23	      2025-10-25    3




# 一 : 对  user_id  分组 ，获取 用户下单2个以及2个以上状态为购买成功 的课程总数并排序;然后子查询查询 每个userId 的

    select user_id, count(*) as cnt
    from order_info
    where status = 'completed'
      and product_name in ('C++', 'Python', 'Java')
    group by user_id
    having cnt >= 2 order by  user_id;

    # +-------+---+
    # |user_id|cnt|
    # +-------+---+
    # |57     |2  |
    # |557336 |3  |
    # +-------+---+

#    根据 user_id 分组，内部排序
    select id , user_id ,date,rank() over (partition by user_id order by date  asc) as ran
    from order_info
    where status = 'completed'
      and product_name in ('C++', 'Python', 'Java') ;

        #     +--+---------+----------+---+
        # |id|user_id  |date      |ran|
        # +--+---------+----------+---+
        # |4 |57       |2025-10-23|1  |
        # |6 |57       |2025-10-24|2  |
        # |5 |557336   |2025-10-23|1  |
        # |7 |557336   |2025-10-25|2  |
        # |8 |557336   |2025-10-25|2  |
        # |2 |230173543|2025-10-12|1  |
        # +--+---------+----------+---+


#  子查询 拼接结果

  select user_id,

         (select date from (  select id , user_id ,date,rank() over (partition by user_id order by date  asc) as ran
    from order_info
    where status = 'completed'
      and product_name in ('C++', 'Python', 'Java') ) as b where b.user_id =a.user_id   and  b.date > '2025-10-15' and  ran = 1 limit 1 ) as  first_buy_date ,
          (select date from (  select id , user_id ,date,rank() over (partition by user_id order by date  asc) as ran
    from order_info
    where status = 'completed'
      and product_name in ('C++', 'Python', 'Java') ) as b where b.user_id =a.user_id and  b.date > '2025-10-15' and  ran = 2 limit 1) as  second_buy_date ,
         count(*) as cnt
    from order_info as  a
    where status = 'completed'
      and product_name in ('C++', 'Python', 'Java') and  date > '2025-10-15'
    group by user_id
    having cnt >= 2 order by  user_id;

    # +-------+--------------+---------------+---+
    # |user_id|first_buy_date|second_buy_date|cnt|
    # +-------+--------------+---------------+---+
    # |57     |2025-10-23    |2025-10-24     |2  |
    # |557336 |2025-10-23    |2025-10-25     |3  |
    # +-------+--------------+---------------+---+

# 法二： 目标只要最小的和 第二小的date

select
 a.user_id,
 min(a.date) as first_buy_date,
 max(a.date) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date>='2025-10-16'
      and status='completed'
      and product_name in('C++','Java','Python')
    ) a
where a.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;
guoyiguang2
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
牛客-sql(困难难度)-2022-03-03

1.SQL91 获得积分最多的人(三):(个人难度: 三星)推荐思路儿解法(高赞解答)drop table if exists user;drop table if exists grade_info;CREATE TABLE user (id int(4) NOT NULL,name varchar(32) NOT NULL);CREATE TABLE grade_info (user_id int(4) NOT NULL,grade_num int(4) NOT NUL
复制链接

扫一扫