【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 可能不一样

)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

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升序排列

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

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升序排序。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

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累计和，其他以此类推。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

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位之后的四舍五入)，并且按照日期升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

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升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

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位之后的四舍五入)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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     );

　　

牛客每个人最近的登录日期(四)

查询每个日期登录新用户个数，并且查询结果按照日期升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

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;

　　

 

牛客每个人最近的登录日期(六)

查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，

并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

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升序排序

完全理解不了解题思路了已经。。。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

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升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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)升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

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升序输出

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

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升序排序

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

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升序排列。你返回的结果中不应当包含重复项

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

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