my sql 重点积累

最新推荐文章于 2024-10-09 10:32:38 发布

rella1103

最新推荐文章于 2024-10-09 10:32:38 发布

阅读量257

点赞数

分类专栏： mysql 踩坑文章标签： mysql sql

本文链接：https://blog.csdn.net/rella1103/article/details/115356504

版权

mysql 踩坑专栏收录该内容

2 篇文章 0 订阅

订阅专栏

这篇博客主要介绍了多个复杂的MySQL查询案例，包括使用窗口函数计算累计薪资，选举骑士的投票统计，按名字排序的奇数排名，新用户登录统计，留存率计算，用户刷题统计，多条件购买课程统计以及7天连续登录用户的查询方法，展示了各种实用的SQL技巧和思路。

摘要由CSDN通过智能技术生成

1.题目描述按照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  emp_no,salary,
        sum(salary) over (order by emp_no) as runing_total
from salaries
where to_date = '9999-01-01'

方法二：

select  emp_no,salary,(
        select sum(s2.salary) from salaries as s2
        where s2.emp_no<=s1.emp_no 
        and to_date = '9999-01-01') as running_total
from salaries as s1
where to_date = '9999-01-01'
order by s1.emp_no

2.四个人选举出一个骑士，统计投票数，并输出真正的骑士名字
投票统计结果表：
在这里插入图片描述

select r.who,count(r.who) as p from test.result as r
group by who
order by p desc
limit 1

显示结果：
在这里插入图片描述
3.对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name
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));

select e1.first_name from employees as e1
where ( select count(*) from employees as e2 
         where e2.first_name<=e1.first_name)%2=1

4.牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下:
在这里插入图片描述

你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:
在这里插入图片描述
思路：1.按照id group by,找到首次登录的时间；2.再按照时间group by 找到有新客登录的时间、新客数量；3.时间清单与有新客的时间清单关联查询

 select b.date as date,ifnull(c.new,0) as new from
 (select distinct(l1.date) as date from login as  l1) b
left join 
 (select a.date ,count(user_id) as new from (select user_id,min(date) as date from login
                                             group by user_id) a
  group by a.date)c
on b.date=c.date
order by b.date

5.留存率：牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
在这里插入图片描述

思路：
1.建立一个用户首次登录表（用户id,用户首次登录时间）；
2.用户首次登录表与总表左连接，连接条件是第一天有登录，第二天也登录；
3.根据关联结果求出有新用户登录时间的留存率；
4.时间清单与以上结果左连接；

select b.date,ifnull(c.p,0) from
  (select distinct(l3.date) as date  from login as l3) b
left join(
   select a.s_date as date , round(count(l2.user_id)*1.0/count(a.user_id),3) as p from (
         select l1.user_id as user_id,min(l1.date) as s_date
         from login as l1
         group by l1.user_id
) a
    left join login l2 on a.user_id=l2.user_id and a.s_date+1=l2.date
    group by a.s_date) c  
on b.date=c.date

6.牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
有一个登录(login)记录表，简况如下:
在这里插入图片描述

select u.name u_n,pn.date,
      sum(number) over(partition by pn.user_id order by pn.date) as pn_num 
from passing_number pn 
join user u on pn.user_id=u.id
group by u_n,pn.date
order by pn.date asc,u_n asc

7.写出一个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升序排序
以上例子查询结果如下: 在这里插入图片描述
以上例子查询结果如下:

思路：1.根据条件筛选建立临时表字段：user_id,date,用户第几次购买，总共购买次数；
2.选出次数购买2次及以上的user_id,首次购买的date,购买次数；
3.选出次数购买2次及以上的user_id,第二次购买的date；
4.两个表一起提取

select t1.user_id,t1.date as first_buy_date,t2.date as second_buy_date,t1.cnt
from
   (select a1.user_id,a1.date ,a1.cnt from    
         (select user_id ,date,
                 row_number() over(partition by user_id order by date ) as t_rank,
                 count(user_id) over (partition by user_id ) as cnt
          from order_info o
          where date>"2025-10-15" and status="completed" 
          and product_name in ('C++','Java','Python')
         ) a1
    where a1. cnt>=2 and a1.t_rank=1) t1, 
   (select a2.user_id,a2.date from    
          (select user_id ,date,
                  row_number() over(partition by user_id order by date ) as t_rank,
                  count(user_id) over (partition by user_id ) as cnt
                  from order_info o
           where date>"2025-10-15" and status="completed" 
           and product_name in ('C++','Java','Python')
           ) a2
    where a2. cnt>=2 and a2.t_rank=2) t2
 where t1.user_id=t2.user_id
order by t1.user_id

8.查询7天连续登陆用户
思路：
1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

select DISTINCT date(date) as 日期,id from orde;

记为表a

2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

select *,row_number() over(PARTITION by id order by 日期) as cum 
from (select DISTINCT date(date) as 日期,id from orde)a;

3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

select *,date(日期)-cum as 结果 
from (
      select *,row_number() over(PARTITION by id order by 日期) as cum from 
                  (
                   select DISTINCT date(date) as 日期,id from orde
                   )a
       )b;

4、按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。

select id,count(*) from 
    (select *,date(日期)-cum as 结果 from 
           (select *,row_number() over(PARTITION by id order by 日期) as cum from 
                 (select DISTINCT date(date) as 日期,id from orde)a
            )b
     )c
GROUP BY id,结果 having count(*)>=7;