现有员工表employees如下:
有员工奖金表emp_bonus:
有薪水表salaries:
其中bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 to_date='9999-01-01’表示当前薪水。
请你给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
bonus结果保留一位小数,输出结果按emp_no升序排序。
以上数据集的输出结果如下
本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时,得到 salary * 0.1;当 btype = 2 时,得到 salary * 0.2;其他情况得到 salary * 0.3。详细用法请参考:
http://www.sqlite.org/lang_expr.html 中的【The CASE expression】
http://www.2cto.com/database/201202/120267.html 中的【条件表达式】
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(CASE b.btype
WHEN 1 THEN s.salary * 0.1
WHEN 2 THEN s.salary * 0.2
ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
其实观察测试数据会发现 btype 只有1,2,3三种情况,即使不会 CASE 表达式,也能运用四则运算解出:(注意要除以10.0,如果除以10的话,结果的小数位会被舍去)
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
有一个薪水表salaries简况如下:
对所有员工的薪水按照salary降序进行1-N的排名,要求相同salary并列,且按照emp_no升序排列:
这题重点技巧如何计算出【1,2,2,3】这种不跳数字的排序
select t1.emp_no, t1.salary, count( distinct t2.salary) as t_rank
from salaries t1,salaries t2
where t1.salary <= t2.salary
group by t1.emp_no,t1.salary
order by t1.salary desc, t1.emp_no
t1.salary <= t2.salary找出在查询t1.salary的时候有多少个t2.salary大于等于t1.salary。这里需要用distinct对t2.salary去重,不然出现的是【1,3,3,4】这种跳数字的排序。因为我们使用了聚合函数count()如果不加group by t1.emp_no的话,只会返回一条结果。同时goup by t1.salary也应该加上,因为salary属于列的字节名,不是主键且不唯一。而select子句只能存在常数、聚合函数、group by子句指定列(聚合键)。所以这里应该填上group by t1.salary。否则可能会出现记录不匹配的情况
select emp_no,
salary,
dense_rank() over(order by salary desc) as t_rank
from salaries
DENSE_RANK()是一个窗口函数,它为分区或结果集中的每一行分配排名,而排名值没有间隙。
行的等级从行前的不同等级值的数量增加1。
引用看到其他小伙伴的贡献:
1、RANK()
在计算排序时,若存在相同位次,会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,4······
2、DENSE_RANK()
这就是题目中所用到的函数,在计算排序时,若存在相同位次,不会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如,有3条排在第1位时,排序为:1,2,3,4······
牛客每天有很多用户刷题,发帖,点赞,点踩等等,这些都会记录相应的积分。
有一个用户表(user),简况如下
还有一个积分表(grade_info),简况如下:
第1行表示,user_id为1的用户积分增加了3分。
第2行表示,user_id为2的用户积分增加了3分。
第3行表示,user_id为1的用户积分减少了1分。
…
最后1行表示,user_id为3的用户积分减少了1分。
请你写一个SQL查找积分最高的用户的id,名字,以及他的总积分是多少(可能有多个),查询结果按照id升序排序,以上例子查询结果如下
1、创建t1表,取出user_id,和通过if添加正负号的分数grade_num。
2、分组求和得到user_id及对应的总分数grade_sum,并通过WITH……AS……打包成临时表t,方便后续多次引用
3、WHERE筛选出grade_sum=最大值的行,连接并取出题目所需列
WITH t AS (
SELECT user_id,SUM(grade_num) AS grade_sum
FROM (SELECT user_id,grade_num * IF(type= 'add',1,-1) AS grade_num
FROM grade_info )AS t1
GROUP BY user_id )
SELECT id,name,grade_sum
FROM t
JOIN user u ON u.id = t.user_id
WHERE grade_sum = (SELECT MAX(grade_sum) FROM t)
SELECT u.id, u.name, g.grade_sum
FROM user u,(
select user_id, sum(if(type='add',grade_num,-grade_num)) grade_sum,
dense_rank() over (order by sum(if(type='add',grade_num,-grade_num)) desc) rk
from grade_info
group by user_id) g
WHERE u.id=g.user_id and g.rk=1
ORDER BY u.id;
有一个,部门关系表dept_emp简况如下:
有一个部门经理表dept_manager简况如下:
有一个薪水表salaries简况如下:
获取员工其当前的薪水比其manager当前薪水还高的相关信息,
第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
以上例子输出如下:.
本题主要思想是创建两张表(一张记录当前所有员工的工资,另一张只记录部门经理的工资)进行比较,具体思路如下:
1、先用INNER JOIN连接salaries和demp_emp,建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager,建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary >
sdm.salary找出同一部门中工资比经理高的员工,并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no,
sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no
FROM salaries s INNER JOIN dept_emp de
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem,
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s
INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary
牛客每天有很多人登录,请你统计一下牛客每个日期登录新用户个数,
有一个登录(login)记录表,简况如下
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网,因为是第1次登录,所以是新用户
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网,因为是第2次登录,所以是老用户
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网,因为是第2次登录,所以是老用户
请你写出一个sql语句查询每个日期登录新用户个数,并且查询结果按照日期升序排序,上面的例子查询结果如下
在每一个date分组中,有数条记录
这些记录中,只有满足用户的date是首次(min)的,才被记录
select date,sum(case
when (user_id,date) in (select user_id,min(date) from login group by user_id)
then 1
else 0
end) as new
from login
group by date
order by date
有很多同学在牛客购买课程来学习,购买会产生订单存到数据库里。
有一个订单信息表(order_info),简况如下
第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单,但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单,状态为购买成功。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了C++课程的订单,状态为购买成功。
请你写出一个sql语句查询在2025-10-15以后,同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息,并且按照order_info的id升序排序,以上例子查询结果如
select t.id,t.user_id,t.product_name,t.status,t.client_id,t.date
from
(select *, count(*) over(partition by user_id) as num
from order_info
where product_name in ('C++','Java','Python')
and status='completed'
and date >'2025-10-15')t
where t.num>=2
order by t.id
有很多同学在牛客购买课程来学习,购买会产生订单存到数据库里。
有一个订单信息表(order_info),简况如下:
第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的非拼团(is_group_buy为No)订单,但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的非拼团(is_group_buy为No)订单,状态为购买成功。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了下了C++课程的拼团(is_group_buy为Yes)订单,拼团不统计客户端,所以client_id所以为0,状态为购买成功。
有一个客户端表(client),简况如下
请你写出一个sql语句查询在2025-10-15以后,同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息,第一列是显示的是客户端名字,如果是拼团订单则显示GroupBuy,第二列显示这个客户端(或者是拼团订单)有多少订单,最后结果按照第一列(source)升序排序,以上例子查询结果如下
解析:
id为4,6的订单满足以上条件,且因为4是通过IOS下单的非拼团订单,则记: IOS 1,6是通过PC下单的非拼团订单,则记: PC 1;id为5,7的订单满足以上条件,且因为5与7都是拼团订单,则记: GroupBuy 2;最后按照source升序排序
select
if(name is null,'GroupBuy',name) as source,count(1) as cnt
## 修改null值为GroupBuy
from(
select
user_id,is_group_buy,name,count(user_id) over(partition by user_id) as cnt
FROM
`order_info`
left join client
on
order_info.client_id = client.id
where
date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')
) test
where cnt>1
## 筛选出大于等于2的
group by name
## 分组
order by source
## 排序
牛客每天有很多人登录,请你统计一下牛客新登录用户的次日成功的留存率,
有一个登录(login)记录表,简况如下:
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
第4行表示user_id为2的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入),上面的例子查询结果如下
(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’),四舍五入的函数为round,sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY),四舍五入的函数为round
select
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);
留存率 = 第一天且下一天也登陆的用户数 / 总用户数
总用户数
SELECT COUNT(DISTINCT user_id) FROM login
第一天且下一天也登陆的用户数
SELECT COUNT(DISTINCT user_id)
FROM login
WHERE (user_id, date) in (
select user_id, DATE_ADD(min(date), INTERVAL 1 DAY)
from login
GROUP by user_id
)
汇总两部分代码,计算留存率
SELECT ROUND(COUNT(DISTINCT user_id) * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3) as p
FROM login
WHERE (user_id, date) in (
select user_id, DATE_ADD(min(date), INTERVAL 1 DAY)
from login
GROUP by user_id
)