【SQL】 牛客网SQL训练Part3 较难难度
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:
（温馨提示:
sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，
select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。
如果使用非group by的列名，sqlite的结果和mysql 可能不一样
)
drop table if exists `employees` ; drop table if exists `salaries` ; 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`)); INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'); INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'); INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'); INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-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,43311,'2001-12-01','9999-01-01'); INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01'); 请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成， 以上例子输出为: （温馨提示: sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时， select子句中一般只能存在以下三种元素： 常数、聚合函数，group by 指定的列名。 如果使用非group by的列名，sqlite的结果和mysql 可能不一样) -- 1、查询最大薪资值 SELECT MAX(`salary`) FROM `salaries` WHERE `to_date` = '9999-01-01' -- 2、查询第二大的薪资值 SELECT MAX(`salary`) FROM `salaries` WHERE `to_date` = '9999-01-01' AND `salary` < ( SELECT MAX(`salary`) FROM `salaries` WHERE `to_date` = '9999-01-01' ) -- 3、联表查询 SELECT e.emp_no, s.salary, e.last_name, e.first_name FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no WHERE s.salary = ( SELECT MAX(`salary`) FROM `salaries` WHERE `to_date` = '9999-01-01' AND `salary` < ( SELECT MAX(`salary`) FROM `salaries` WHERE `to_date` = '9999-01-01' ) )
对所有员工的薪水按照salary降序进行1-N的排名
所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列
drop table if exists `salaries` ;
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 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,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');
-- MYSQL8版本支持
SELECT
emp_no,
salary,
dense_rank ( ) over ( ORDER BY salary DESC ) AS rank
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY rank ASC, emp_no ASC;
-- MYSQL5支持 rank排名：查询表中大于自己薪水的员工的数量（考虑并列：去重）
SELECT
s1.emp_no,
s1.salary,
(SELECT
COUNT(DISTINCT s2.salary)
FROM salaries s2
WHERE
s2.to_date = '9999-01-01'
AND s2.salary >= s1.salary) AS `rank` -- 去重：计算并列排名
FROM salaries s1
WHERE s1.to_date = '9999-01-01'
ORDER BY
s1.salary DESC,
s1.emp_no ;
获取所有非manager员工当前的薪水情况
获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary
drop table if exists `dept_emp` ;
drop table if exists `dept_manager` ;
drop table if exists `employees` ;
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 `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 `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`));
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_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1996-08-03');
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
-- 4表连接
SELECT
D.dept_no,
E.emp_no,
S.salary
FROM
employees AS E
LEFT JOIN dept_manager AS MGR ON E.emp_no = MGR.emp_no
LEFT JOIN salaries AS S ON E.emp_no = S.emp_no
LEFT JOIN dept_emp AS D ON E.emp_no = D.emp_no
WHERE
MGR.dept_no IS NULL
获取有奖金的员工相关信息。
其中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升序排序。
drop table if exists `employees` ;
drop table if exists emp_bonus;
drop table if exists `salaries` ;
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 emp_bonus(
emp_no int not null,
recevied 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`));
insert into emp_bonus values
(10001, '2010-01-01',1),
(10002, '2010-10-01',2);
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
-- 主要是考察CASE WHEN 语法
SELECT
E.emp_no,
E.first_name,
E.last_name,
B.btype,
S.salary,
(
CASE B.btype
WHEN 1 THEN ROUND(S.salary * 0.1, 4)
WHEN 2 THEN ROUND(S.salary * 0.2, 4)
ELSE ROUND(S.salary * 0.3, 4)
END
) AS bonus
FROM
employees AS E
LEFT JOIN salaries AS S ON E.emp_no = S.emp_no
LEFT JOIN emp_bonus AS B ON E.emp_no = B.emp_no
WHERE
S.to_date = '9999-01-01'
ORDER BY E.emp_no ASC
统计salary的累计和running_total
按照salary的累计和running_total，
其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。
drop table if exists `salaries` ; 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 salaries VALUES(10001,60117,'1986-06-26','1987-06-26'); INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25'); INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25'); INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25'); INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25'); INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24'); INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24'); INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24'); INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24'); INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23'); INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23'); INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23'); INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23'); INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22'); INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22'); INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22'); INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03'); INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03'); INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02'); INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02'); INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02'); INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02'); INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02'); INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02'); INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01'); INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01'); INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01'); -- 查询running_total为前N个当前( to_date = '9999-01-01')员工的salary累计 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_total FROM salaries AS s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no
给出employees表中排名为奇数行的first_name
drop table if exists `employees` ;
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
-- 主要实现SQL1 - MYSQL8开窗函数实现
SELECT
first_name,
row_number ( ) over ( ORDER BY first_name ) AS rank_num
FROM employees
-- 主要实现SQL1 - 不使用MYSQL8开窗函数，设置变量实现
SET @orderId = 0;
SELECT
first_name,
@orderId:= @orderId + 1 AS `rank`
FROM `employees`
ORDER BY first_name
-- 解决SQL：
SELECT E.first_name
FROM
employees AS E JOIN
(
-- 上面两种SQL任选一种作为联表查询
) AS E2 ON E.first_name = E2.first_name
WHERE E2.`rank` % 2 = 1;
-- 直接子查询嵌套会因为不是原表顺序，答案不符合
SELECT
e.first_name
FROM employees e JOIN
(
SELECT
first_name,
ROW_NUMBER() OVER(ORDER BY first_name ASC) AS r_num
FROM employees
) AS t
ON e.first_name = t.first_name
WHERE t.r_num % 2 = 1;
异常的邮件概率
每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，
结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序
drop table if exists email; drop table if exists user; CREATE TABLE `email` ( `id` int(4) NOT NULL, `send_id` int(4) NOT NULL, `receive_id` int(4) NOT NULL, `type` varchar(32) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `user` ( `id` int(4) NOT NULL, `is_blacklist` int(4) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO email VALUES (1,2,3,'completed','2020-01-11'), (2,1,3,'completed','2020-01-11'), (3,1,4,'no_completed','2020-01-11'), (4,3,1,'completed','2020-01-12'), (5,3,4,'completed','2020-01-12'), (6,4,1,'completed','2020-01-12'); INSERT INTO user VALUES (1,0), (2,1), (3,0), (4,0); -- 1、联表实现筛选白名单用户 SELECT `email`.* FROM `email` JOIN `user` AS u1 ON ( email.send_id = u1.id AND u1.is_blacklist = 0 ) JOIN `user` AS u2 ON ( email.receive_id = u2.id AND u2.is_blacklist = 0 ) -- 2、子查询实现筛选 -- 查询黑名单用户，或者正常用户 SELECT id FROM `user` WHERE `is_blacklist` = 1 -- 在EMAIL表中筛选正常用户来往的邮件记录 SELECT `email`.* FROM `email` WHERE email.send_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) AND email.receive_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) -- 3、按日期分组进行筛选，直接计数异常次数和总次数即可实现 SELECT `email`.date, SUM(`email`.type = 'no_completed' ) AS `异常次数`, COUNT(`email`.type) AS `总计次数`, ROUND (SUM(`email`.type = 'no_completed' ) * 1.0 / COUNT(`email`.type), 3 )AS `概率` FROM `email` WHERE email.send_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) AND email.receive_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) GROUP BY `email`.date ORDER BY `email`.date -- 最终SQL SELECT `email`.date, ROUND (SUM(`email`.type = 'no_completed' ) * 1.0 / COUNT(`email`.type), 3 )AS `p` FROM `email` WHERE email.send_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) AND email.receive_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) GROUP BY `email`.date ORDER BY `email`.date -- 或者按非异常次数查询 SELECT `email`.date, SUM( CASE `email`.type WHEN 'completed' THEN 0 ELSE 1 END ) AS `总计`, COUNT(`email`.type) AS `次数`, ROUND (SUM( CASE `email`.type WHEN 'completed' THEN 0 ELSE 1 END ) * 1.0 / COUNT(`email`.type), 3 )AS `概率` FROM `email` WHERE email.send_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) AND email.receive_id NOT IN (SELECT id FROM `user` WHERE `is_blacklist` = 1) GROUP BY `email`.date ORDER BY `email`.date
牛客每个人最近的登录日期(二)
查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，
并且查询结果按照user的name升序排序
drop table if exists login; drop table if exists user; drop table if exists client; CREATE TABLE `login` ( `id` int(4) NOT NULL, `user_id` int(4) NOT NULL, `client_id` int(4) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `user` ( `id` int(4) NOT NULL, `name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `client` ( `id` int(4) NOT NULL, `name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO login VALUES (1,2,1,'2020-10-12'), (2,3,2,'2020-10-12'), (3,2,2,'2020-10-13'), (4,3,2,'2020-10-13'); INSERT INTO user VALUES (1,'tm'), (2,'fh'), (3,'wangchao'); INSERT INTO client VALUES (1,'pc'), (2,'ios'), (3,'anroid'), (4,'h5'); -- 全部连接起来筛选最近日期 SELECT U.`name`, C.`name` AS `client`, L.`date` FROM `user` AS U JOIN login AS L ON U.id = L.user_id JOIN client AS C ON L.client_id = C.id WHERE L.`date` = (SELECT MAX(date) FROM login) ORDER BY U.`name` ASC
牛客每个人最近的登录日期(三)
查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)
drop table if exists login; CREATE TABLE `login` ( `id` int(4) NOT NULL, `user_id` int(4) NOT NULL, `client_id` int(4) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); INSERT INTO login VALUES (1,2,1,'2020-10-12'), (2,3,2,'2020-10-12'), (3,1,2,'2020-10-12'), (4,2,2,'2020-10-13'), (5,4,1,'2020-10-13'), (6,1,2,'2020-10-13'), (7,1,2,'2020-10-14'); 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 );
牛客每个人最近的登录日期(四)
查询每个日期登录新用户个数，并且查询结果按照日期升序排序
drop table if exists login; CREATE TABLE `login` ( `id` int(4) NOT NULL, `user_id` int(4) NOT NULL, `client_id` int(4) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); INSERT INTO login VALUES (1,2,1,'2020-10-12'), (2,3,2,'2020-10-12'), (3,1,2,'2020-10-12'), (4,2,2,'2020-10-13'), (5,1,2,'2020-10-13'), (6,3,1,'2020-10-14'), (7,4,1,'2020-10-14'), (8,4,1,'2020-10-15'); -- 拿到首次登录用户和时间 SELECT `user_id`, MIN( `date` ) FROM `login` GROUP BY user_id -- 1、可以对Login表的日期分组，用户和日期可以用IN和上述查询结果进行匹配处理 -- 2、如果匹配成功，表示这个用户是在这个日期是首次登录 -- 3、ELSE NULL 不能写 ELSE 0，因为0就会算一条记录，NULL则不被COUNT函数计数 SELECT `date`, COUNT( DISTINCT CASE WHEN ( user_id, date ) IN ( SELECT user_id, min( date ) FROM login GROUP BY user_id ) THEN `user_id` ELSE NULL END ) AS `new` FROM login GROUP BY `date` ORDER BY `date` ASC;
牛客每个人最近的登录日期(六)
查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，
并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出
drop table if exists login; drop table if exists passing_number; drop table if exists user; drop table if exists client; CREATE TABLE `login` ( `id` int(4) NOT NULL, `user_id` int(4) NOT NULL, `client_id` int(4) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `passing_number` ( `id` int(4) NOT NULL, `user_id` int(4) NOT NULL, `number` int(4) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `user` ( `id` int(4) NOT NULL, `name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO login VALUES (1,2,1,'2020-10-12'), (2,3,2,'2020-10-12'), (3,1,2,'2020-10-12'), (4,1,3,'2020-10-13'), (5,3,2,'2020-10-13'); INSERT INTO passing_number VALUES (1,2,4,'2020-10-12'), (2,3,1,'2020-10-12'), (3,1,0,'2020-10-13'), (4,3,2,'2020-10-13'); INSERT INTO user VALUES (1,'tm'), (2,'fh'), (3,'wangchao'); -- 1、使用开窗函数 SELECT `name` AS `u_n`, `date`, SUM(number) OVER (PARTITION BY `user_id` ORDER BY date) AS `ps_num` FROM `passing_number` AS `p` INNER JOIN `user` ON `user`.`id` = `p`.`user_id` GROUP BY `date`, `u_n` ORDER BY `date`, `name` -- 2、自连接实现 SELECT `name` AS u_n,date, p.ps_num FROM ( SELECT p1.user_id, p1.date, SUM(p2.number) AS ps_num FROM passing_number AS p1, passing_number AS p2 WHERE p1.date >= p2.date AND p1.user_id = p2.user_id GROUP BY p1.date,p1.user_id ) AS p JOIN `user` ON `user`.id = p.`user_id` ORDER BY p.date, `user`.`name`
考试分数(三)
找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
完全理解不了解题思路了已经。。。
drop table if exists grade; drop table if exists language; CREATE TABLE `grade` ( `id` int(4) NOT NULL, `language_id` int(4) NOT NULL, `score` int(4) NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `language` ( `id` int(4) NOT NULL, `name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO grade VALUES (1,1,12000), (2,1,13000), (3,2,11000), (4,2,10000), (5,3,11000), (6,1,11000), (7,2,11000); INSERT INTO language VALUES (1,'C++'), (2,'JAVA'), (3,'Python'); SELECT g1.id, l.NAME, g1.score FROM grade g1 JOIN `language` l ON g1.language_id = l.id WHERE ( SELECT count( DISTINCT g2.score ) FROM grade g2 WHERE g2.score >= g1.score AND g1.language_id = g2.language_id ) <= 2 ORDER BY l.NAME, g1.score DESC, g1.id;
考试分数(四)
查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序
drop table if exists grade; CREATE TABLE grade( `id` int(4) NOT NULL, `job` varchar(32) NOT NULL, `score` int(10) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO grade VALUES (1,'C++',11001), (2,'C++',10000), (3,'C++',9000), (4,'Java',12000), (5,'Java',13000), (6,'B',12000), (7,'B',11000), (8,'B',9999); -- 题解 SELECT job, FLOOR( (COUNT(1) + 1) / 2) AS 'start', CEIL( (COUNT(1) + 1) / 2) AS 'end' FROM grade GROUP BY job ORDER BY job; +------+-------+-----+ | job | start | end | +------+-------+-----+ | B | 2 | 2 | | C++ | 2 | 2 | | Java | 1 | 2 | +------+-------+-----+ 3 rows in set (0.05 sec)
牛客的课程订单分析(四)
查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及所有日期里购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序
drop table if exists order_info; CREATE TABLE order_info ( id int(4) NOT NULL, user_id int(11) NOT NULL, product_name varchar(256) NOT NULL, status varchar(32) NOT NULL, client_id int(4) NOT NULL, date date NOT NULL, PRIMARY KEY (id)); INSERT INTO order_info VALUES (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'); -- 直接分组之后筛选对应条件，取MIN(date) 和 COUNT（product_name） SELECT user_id, MIN(date) AS `first_buy_date`, COUNT(product_name) AS `cnt` FROM order_info WHERE date > '2025-10-15' AND STATUS = 'completed' AND product_name IN ( 'C++', 'Java', 'Python' ) GROUP BY user_id HAVING COUNT( user_id ) > 1 ; +---------+----------------+-----+ | user_id | first_buy_date | cnt | +---------+----------------+-----+ | 57 | 2025-10-23 | 2 | | 557336 | 2025-10-23 | 3 | +---------+----------------+-----+ 2 rows in set (0.05 sec)
牛客的课程订单分析(七)
查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，
第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，
最后结果按照第一列(source)升序排序
drop table if exists order_info; drop table if exists client; CREATE TABLE order_info ( id int(4) NOT NULL, user_id int(11) NOT NULL, product_name varchar(256) NOT NULL, status varchar(32) NOT NULL, client_id int(4) NOT NULL, date date NOT NULL, is_group_buy varchar(32) NOT NULL, PRIMARY KEY (id)); CREATE TABLE client( id int(4) NOT NULL, name varchar(32) NOT NULL, PRIMARY KEY (id) ); INSERT INTO order_info VALUES (1,557336,'C++','no_completed',1,'2025-10-10','No'), (2,230173543,'Python','completed',2,'2025-10-12','No'), (3,57,'JS','completed',0,'2025-10-23','Yes'), (4,57,'C++','completed',3,'2025-10-23','No'), (5,557336,'Java','completed',0,'2025-10-23','Yes'), (6,57,'Java','completed',1,'2025-10-24','No'), (7,557336,'C++','completed',0,'2025-10-25','Yes'); INSERT INTO client VALUES (1,'PC'), (2,'Android'), (3,'IOS'), (4,'H5'); -- SELECT IF(a.client_id = 0,'GroupBuy',c.name) AS source, COUNT(*) AS cnt FROM ( SELECT client_id FROM order_info WHERE date > '2025-10-15' AND STATUS = 'completed' AND product_name IN ( 'C++', 'Java', 'Python' ) AND user_id IN ( SELECT user_id FROM order_info WHERE STATUS = 'completed' GROUP BY user_id HAVING COUNT( * ) >= 2 ) ) AS a LEFT JOIN client c ON a.client_id = c.id GROUP BY a.client_id ORDER BY source
最差是第几名(二)
查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出
drop table if exists class_grade;
CREATE TABLE class_grade (
grade varchar(32) NOT NULL,
number int(4) NOT NULL
);
INSERT INTO class_grade VALUES
('A',2),
('C',4),
('B',4),
('D',2);
-- 题解
SELECT
grade
FROM
(
SELECT
grade,
( SELECT SUM(number) FROM class_grade c1 WHERE c1.grade < c2.grade ) AS s,
( SELECT SUM(number) FROM class_grade c1 WHERE c1.grade <= c2.grade ) AS e
FROM class_grade c2
ORDER BY grade
) AS t1,
(
SELECT sum( number ) AS sums
FROM class_grade
) AS t2
WHERE
( sums / 2 ) BETWEEN s AND e
获得积分最多的人
查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序
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,'add'), (3,3,'add'), (4,3,'add'), (5,3,'add'), (3,1,'add'); -- 1、获取所有人积分 SELECT `user_id`, SUM(`grade_num`) AS `total` FROM grade_info WHERE `type` = 'add' GROUP BY `user_id` ORDER BY `total` DESC -- 2、获取积分最大值 SELECT SUM(`grade_num`) AS `grade_sum` FROM grade_info WHERE `type` = 'add' GROUP BY user_id ORDER BY `grade_sum` DESC LIMIT 1 -- 3、子查询筛选符合的用户 SELECT `user_id`, SUM(`grade_num`) AS `total` FROM grade_info WHERE `type` = 'add' GROUP BY `user_id` HAVING `total` = ( SELECT SUM(`grade_num`) AS `grade_sum` FROM grade_info WHERE `type` = 'add' GROUP BY user_id ORDER BY `grade_sum` DESC LIMIT 1 ) ORDER BY `total` DESC, `user_id` ASC -- 最终解题SQL SELECT `user`.`name`, `main`.`total` FROM ( SELECT `user_id`, SUM(`grade_num`) AS `total` FROM grade_info WHERE `type` = 'add' GROUP BY `user_id` HAVING `total` = ( SELECT SUM(`grade_num`) AS `grade_sum` FROM grade_info WHERE `type` = 'add' GROUP BY user_id ORDER BY `grade_sum` DESC LIMIT 1 ) ORDER BY `total` DESC, `user_id` ASC ) AS `main` JOIN `user` ON `main`.`user_id` = `user`.`id`
网易云音乐推荐(网易校招笔试真题)
查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项
CREATE TABLE `follow` ( `user_id` int(4) NOT NULL, `follower_id` int(4) NOT NULL, PRIMARY KEY (`user_id`,`follower_id`)); CREATE TABLE `music_likes` ( `user_id` int(4) NOT NULL, `music_id` int(4) NOT NULL, PRIMARY KEY (`user_id`,`music_id`)); CREATE TABLE `music` ( `id` int(4) NOT NULL, `music_name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO follow VALUES(1,2); INSERT INTO follow VALUES(1,4); INSERT INTO follow VALUES(2,3); INSERT INTO music_likes VALUES(1,17); INSERT INTO music_likes VALUES(2,18); INSERT INTO music_likes VALUES(2,19); INSERT INTO music_likes VALUES(3,20); INSERT INTO music_likes VALUES(4,17); INSERT INTO music VALUES(17,'yueyawang'); INSERT INTO music VALUES(18,'kong'); INSERT INTO music VALUES(19,'MOM'); INSERT INTO music VALUES(20,'Sold Out'); -- ID为1的用户的关注人 SELECT `follower_id` FROM follow WHERE `user_id` = 1 -- ID为1的用户 喜欢的音乐 SELECT music_id FROM music_likes WHERE `user_id` = 1 -- 关注的人喜欢的音乐 SELECT music_id FROM music_likes WHERE `user_id` IN ( SELECT `follower_id` FROM follow WHERE `user_id` = 1 ) -- 去掉用户自己的音乐, 就是题目需要的歌曲了 SELECT music_id FROM music_likes WHERE `user_id` IN ( SELECT `follower_id` FROM follow WHERE `user_id` = 1 ) AND music_id NOT IN ( SELECT music_id FROM music_likes WHERE `user_id` = 1 ) -- 联表得出最终结果 SELECT music.`music_name` FROM ( SELECT music_id FROM music_likes WHERE `user_id` IN ( SELECT `follower_id` FROM follow WHERE `user_id` = 1 ) AND music_id NOT IN ( SELECT music_id FROM music_likes WHERE `user_id` = 1 ) ) AS `main` JOIN `music` ON `main`.music_id = `music`.id ORDER BY `music`.id ASC
