![](https://img-blog.csdnimg.cn/20201014180756925.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL刷题
练习逻辑能力,加深对数据的理解
yuxiaoyanran2020
这个作者很懒,什么都没留下…
展开
-
牛客SQL大厂面试真题:SQL3 每类视频近一个月的转发量/率
编程代码select b.tag ,sum(a.if_retweet) as retweet_cut ,round(sum(a.if_retweet)/count(a.id),3) as retweet_ratefrom tb_user_video_log as a join tb_video_info as b using (video_id) where datediff((select max(end_time) from tb_user_video_log)原创 2022-04-04 11:05:12 · 654 阅读 · 0 评论 -
SQL实战83:牛客的课程订单分析(七)
题目解答with tmp as (select user_id,count(*) as cntfrom order_infowhere date > "2025-10-15"and product_name in ("C++","Java","Python")and status = "completed"group by user_idhaving cnt >= 2 )select if(t.name is null,"GroupBuy",t.name) as sour原创 2021-09-01 10:01:56 · 110 阅读 · 0 评论 -
SQL实战82. 牛客的课程订单分析(六)
题目解答with tmp as (select user_id,count(*) as cntfrom order_infowhere date > "2025-10-15"and product_name in ("C++","Java","Python")and status = "completed"group by user_idhaving cnt >= 2 )select o.id,o.is_group_buy,c.namefrom order_info a原创 2021-09-01 09:51:50 · 62 阅读 · 0 评论 -
SQL实战91.获得积分最多的人(三)
题目解答with tmp as ( select a.user_id, a.cnt as add_num ,if(b.cnt is null,0,b.cnt) as reduce_num from (select user_id,sum(grade_num) as cnt from grade_info where type = "add" gro原创 2021-04-14 23:29:45 · 535 阅读 · 0 评论 -
SQL实战90.获得积分最多的人(二)
题目解答select b.user_id as id,c.name,b.cntfrom ( select a.user_id,a.cnt,rank() over (order by a.cnt desc) as ranking from ( select user_id,sum(grade_num) as cnt from grade_info where type = "add" g原创 2021-04-14 23:09:48 · 312 阅读 · 0 评论 -
SQL实战88.最差是第几名(二)
题目解答当某一数的正序和逆序的累计均大于等于整个序列的数字个数的一半即为中位数select grade from (select grade,(select sum(number) from class_grade) as total, sum(number) over(order by grade) a, sum(number) over(order by grade desc) b from class_grade) t1where a >原创 2021-04-14 22:51:05 · 202 阅读 · 0 评论 -
SQL实战86.实习广场投递简历分析(三)
题目解答select a.job, a.mon as first_year_mon,a.cnt as first_year_cnt,b.mon as second_year_mon,b.cnt as second_year_cnt from ( select job,date_format(date,"%Y-%m") as mon,sum(num) as cnt from resume_info where date_format(date,"%Y") = "202原创 2021-04-13 22:58:19 · 144 阅读 · 0 评论 -
SQL实战80 牛客的课程订单分析(四)
题目解答select user_id,min(date) as first_buy_date,count(user_id) as cntfrom order_infowhere date > "2025-10-15"and status = "completed"and product_name in ("C++","Java","Python")group by user_idhaving count(user_id) > 1order by user_id;...原创 2021-04-07 22:48:31 · 130 阅读 · 0 评论 -
SQL实战81. 牛客的课程订单分析(五)
题目解答with tmp as ( select user_id,min(date) as first_buy_date,count(user_id) as cnt from order_info where date > "2025-10-15" and status = "completed" and product_name in ("C++","Java","Pytho原创 2021-04-07 22:42:21 · 246 阅读 · 0 评论 -
SQL实战84 实习广场投递简历分析(一)
题目select job,sum(num) as cntfrom resume_infowhere date between "2025-01-01" and "2025-12-31"group by joborder by sum(num) desc;原创 2021-04-05 19:42:53 · 132 阅读 · 0 评论 -
SQL实战70.牛客每个人最近的登录日期(五)
题目解答SELECT a.date,ROUND(COUNT(DISTINCT login.user_id)/ COUNT(a.user_id),3) AS pFROM ( -- 找到每天的新用户 SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id) AS a -- 和原表login联结的条件也容易出错,这里要根据用户id和日期两个条件来进行联结-- 日期的限定条件那里不能使用where,如果使用原创 2020-12-06 22:09:42 · 169 阅读 · 0 评论 -
SQL实战76.考试分数(五)
题目解答select B.* from(SELECT job,FLOOR((COUNT(*)+1)/2) AS `start`,FLOOR((COUNT(*)+1)/2)+if(COUNT(*) % 2=1,0,1) AS `end` FROM grade GROUP BY job) A -- 中位数的位置信息,也就是升序之后的排名信息JOIN(select g1.*, (select count(distinct g2.score) from grade g2原创 2020-12-06 16:19:45 · 167 阅读 · 0 评论 -
SQL实战26.汇总各个部门当前员工的title类型的分配数目
题目解答刚开始写的代码缺少group by行代码,通不过测试,看讨论可知:需要将dept_no作为分组条件才行, 因为不同部门,包含了不同title,首先是根据部门分组,然后同一部分再根据title分组。select d.dept_no,d.dept_name,t.title,count(t.title) as "count"from titles as t join dept_emp as de on t.emp_no=de.emp_no join depar原创 2020-12-05 21:38:55 · 357 阅读 · 0 评论 -
SQL实战67.牛客每个人最近的登录日期(二)
题目解答select u.name u_n,c.name c_n,l.date d from login l inner join (select user_id,max(date) max from login group by user_id) ton l.user_id = t.user_id and l.date = t.maxinner join user u on l.user_id = u.id inner join client c on l.client_id = c.id原创 2020-12-05 17:23:09 · 136 阅读 · 0 评论 -
SQL实战61.对于employees表中,给出奇数行的first_name
题目解答自写答案,通不过测试,看讨论的原因是没有考虑到“输出的结果不需排序”,觉得此理由行不通,因为不需排序包含排序情况,更应该能通过测试。select t.first_namefrom(select e.first_name,row_number () over (order by first_name ) as rankingfrom employees as e) as twhere mod(t.ranking,2)=1指正答案:select e.first_namefrom原创 2020-12-05 16:03:32 · 396 阅读 · 0 评论 -
SQL实战41.构造一个触发器audit_log,在向employees表中插入一条数据的时候,触发插入相关的数据到audit中
题目解答知识补充主要借鉴的是MySQL数据库触发器讲解与案例在MySQL中,创建触发器语法如下:CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROWtrigger_stmt其中:trigger_name:标识触发器名称,用户自行指定;trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;trigger_event:标识触发事件,取值为 INSERT、UP原创 2020-12-05 11:30:55 · 1102 阅读 · 0 评论 -
SQL实战12.获取所有部门中当前员工薪水最高的相关信息
题目解答自写代码:select d.dept_no, d.emp_no, s.salaryfrom dept_emp as dinner join salaries as son d.emp_no=s.emp_nowhere d.to_date='9999-01-01’and s.to_date='9999-01-01'group by d.dept_nohaving salary=max(s.salary);错误点1:d.emp_no是非聚合字段,不能出现在SELECT。因为一个原创 2020-12-05 10:59:07 · 407 阅读 · 0 评论 -
SQL实战75.考试分数(四)
题目解答自写答案,觉得对但是不知为何通不过,之后来纠错select job,case when total%2=1 then (total+1)/2 else total/2 end as "start",case when total%2=1 then (total+1)/2 else (total/2)+1 end as "end"from (select job,count(*) as total from grade group by job) as torder by job原创 2020-12-04 19:16:17 · 96 阅读 · 0 评论 -
SQL实战28.查找描述信息中包括robot的电影对应的分类名称以及电影数目
题目解答注意:原题中的“还需要该分类包含电影总数量(count(film_category.category_id))>=5”,应更改为(count(film_category.film_id))>=5select c.name as '分类名称category.name',count(f.film_id) as '电影数目count(film.film_id)'from film as f join film_category as fc on fc.film_id=f.film原创 2020-12-04 16:40:09 · 498 阅读 · 0 评论 -
SQL实战71.牛客每个人最近的登录日期(六)
题目解答注意:增加条件算累和select u.name as u_n,t.date,t.ps_numfrom (select pn1.user_id,pn1.date,(select sum(pn2.number) from passing_number as pn2 where pn1.date >= pn2.date and pn1.user_id = pn2.user_id) as ps_numfrom passing_number as pn1) as t l原创 2020-12-04 14:51:59 · 104 阅读 · 0 评论 -
SQL实战69.牛客每个人最近的登录日期(四)
题目解答select a.date,sum(case when t_rank=1 then 1 else 0 end) newfrom (select date, row_number() over(partition by user_id order by date) t_rankfrom login) agroup by date;原创 2020-12-03 22:29:33 · 124 阅读 · 0 评论 -
SQL实战68.牛客每个人最近的登录日期(三)
题目解答知识补充代码新登录用户的次日成功的留存率:(第一天登录的新用户并且第二天也登录的用户)/(总用户)select round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)from loginwhere (user_id,date)in (select user_id,date_add(min(date),interval 1 day) from login group by原创 2020-12-03 22:08:45 · 113 阅读 · 0 评论 -
SQL实战74.考试分数(三)
题目解答注意:不能用特殊函数命名某列,比如as rankselect id,name,scorefrom (select g.id,l.name,g.score,dense_rank () over (partition by g.language_id order by score desc) as mfrom grade as g inner join language as l on g.language_id=l.id) as twhere m<=2order by name原创 2020-12-03 19:53:39 · 117 阅读 · 0 评论 -
SQL实战65.异常的邮件概率
题目解答自己的思路联结三张表作为t分母select count(t.type)from twhere t.r_s=0 and t.r_r = 0group by t.date分子select count(t.type)from twhere t.r_s=0 and t.r_r = 0 and t.type="no_completed"group by t.date最后分子分母相除借鉴思路select e.date, round(sum(case e.type when原创 2020-12-03 16:33:57 · 153 阅读 · 0 评论 -
SQL实战60.统计salary的累计和running_total
题目解答注意思考方式select s1.emp_no,s1.salary,(select sum(s2.salary) from salaries as s2 where s2.emp_no<=s1.emp_no and s2.to_date = '9999-01-01') as running_totalfrom salaries as s1where s1.to_date = '9999-01-01'原创 2020-12-02 22:37:33 · 195 阅读 · 0 评论 -
SQL实战59.获取有奖金的员工相关信息
题目解答注意:case的用法(1)case 列名 when 值 then 值(2)case when 列名=值 then 值select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary, (case when eb.btype=1 then s.salary*0.1 when eb.btype=2 then s.salary*0.2 else s.salary*0.3 end) as bonusfro原创 2020-12-02 21:41:40 · 320 阅读 · 0 评论 -
SQL实战56.获取所有员工的emp_no
题目解答注意:易错点是有些员工是没有分配部门的select e.emp_no,d.dept_no,eb.btype,eb.receivedfrom dept_emp as d left join employees as e on d.emp_no=e.emp_no left join emp_bonus as eb on e.emp_no=eb.emp_no;原创 2020-12-02 21:17:03 · 107 阅读 · 0 评论 -
SQL实战27.给出每个员工每年薪水涨幅超过5000的员工编号emp_no
题目解答注意:差一年就是表1截止时间=表二开始时间;提取到的开始时间是涨薪后开始的那一年select s1.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growthfrom salaries as s1 join salaries as s2 on s1.emp_no=s2.emp_noand s2.from_date=s1.to_datewhere s2.salary-s1.salary>5000order by salar原创 2020-12-02 20:58:06 · 485 阅读 · 0 评论 -
SQL实战25.获取员工其当前的薪水比其manager当前薪水还高的相关信息
题目解答思路:创建两张表,一张员工表,一张经理表,进行联结--员工表 (select de.emp_no,de.dept_no,s.salary as emp_salary from dept_emp as de left join salaries as son de.emp_no=s.emp_nowhere de.emp_no not in (select emp_no from dept_manager)and s.to_date='9999-01-01') as a--经理表原创 2020-12-02 19:18:47 · 140 阅读 · 0 评论 -
SQL实战24.获取所有非manager员工当前的薪水情况
题目解答select de.dept_no,e.emp_no,s.salaryfrom dept_emp as de left join employees as e on e.emp_no=de.emp_no left join salaries as s on s.emp_no=e.emp_nowhere s.to_date='9999-01-01'and e.emp_no not in (select emp_no from dept_manager原创 2020-12-02 17:17:58 · 108 阅读 · 0 评论 -
SQL实战23.对所有员工的当前薪水按照salary进行按照1-N的排名
题目解答select emp_no,salary,dense_rank () over (order by salary desc) as t_rankfrom salarieswhere to_date='9999-01-01'order by t_rank,emp_no;原创 2020-12-02 16:30:00 · 113 阅读 · 0 评论 -
SQL实战21.查找所有员工自入职以来的薪水涨幅情况
题目解答注意:入职时的工资:入职时间=薪资开始时间;当前工资:薪资结束时间为无穷select b.emp_no,(b.salary-a.salary) as growthfrom (select emp_no,salary from salaries where to_date = '9999-01-01') as b,(select s.emp_no,s.salary from salaries as s inner join employees as e on s.emp_no=e.em原创 2020-12-02 15:43:40 · 380 阅读 · 0 评论 -
SQL实战18.查找当前薪水排名第二多的员工编号emp_no
题目解决方法一:使用order byselect e.emp_no,s.salary,e.last_name,e.first_namefrom salaries as s left join employees as eon s.emp_no=e.emp_nowhere s.to_date='9999-01-01'order by salary desclimit 1 offset 1;原创 2020-12-02 15:13:12 · 332 阅读 · 0 评论 -
SQL实战46.在audit表上创建外键约束,其emp_no对应employees_test表的主键id
题目解答若表已创建,需先删除表外键约束:foreign key(牵引列) references 外键对应的表名(表名)DROP TABLE audit;CREATE TABLE audit(EMP_no INT NOT NULL,create_date datetime NOT NULL,FOREIGN KRY(EMP_no) REFERENCES employees_test(ID));...原创 2020-12-01 22:27:38 · 91 阅读 · 0 评论 -
SQL实战48.将所有获取奖金的员工当前的薪水增加10%
题目解答注意:设置条件时,不能用salaries.emp_no= emp_bonus.emp_no,要用inupdate salariesset salary=salary*1.1where to_date='9999-01-01'and emp_no in (select emp_no from emp_bonus);原创 2020-12-01 22:11:08 · 1250 阅读 · 0 评论 -
SQL实战50.将employees表中的所有员工的last_name和first_name通过(‘)连接起来。
题目解答select concat_ws("'",last_name,first_name) as namefrom employees;原创 2020-12-01 21:42:22 · 371 阅读 · 0 评论 -
SQL实战51.查找字符串‘10,A,B‘ 中逗号‘,‘出现的次数cnt
题目解答length():统计字符串长度replace(字符串,“需要替换的子串”,“用于替换子串的字符串”)将,替换为空,字符串缩短的长度就是,号的个数select (length("10,A,B") - length(replace("10,A,B",",","")) )as cnt;...原创 2020-12-01 21:36:59 · 135 阅读 · 0 评论 -
SQL实战52.获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
题目解答知识补充代码select first_namefrom employeesorder by substr(first_name,-2);原创 2020-12-01 21:11:08 · 688 阅读 · 0 评论 -
SQL实战53.按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
题目解答知识补充一、使用CONCAT连接:直接连接,没有符号使用方法:CONCAT(str1,str2,…)SELECT CONCAT(id, name, work_date) FROM employee_tbl;二、使用CONCAT_WS连接:使用符号连接使用方法:CONCAT_WS(分隔符,str1,str2,…)注意:如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。和CONCAT函数不同的是, CONCAT_WS函数在执行的时候,不会因为原创 2020-12-01 20:30:35 · 515 阅读 · 0 评论 -
SQL实战54.查找排除当前最大、最小salary之后的员工的平均工资avg_salary
题目解答select avg(salary) as avg_salary from salarieswhere salary !=(select max(salary) from salaries where to_date = '9999-01-01') and salary !=(select min(salary) from salaries where to_date = '9999-01-01') and to_date = '9999-01-01'原创 2020-12-01 09:58:12 · 287 阅读 · 0 评论