细雨噜噜噜

题解

/ 注册

全部文章 / 题解（共96篇）

题解 | #查找入职员工时间排名倒数第三的员工所有信息#

SELECT * FROM employees WHERE hire_date=(SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1); LIMIT m,n: 表示从第m+1条开始，取n条数据；LIMI...

Mysql

2021-09-29

1 294

题解 | #浙大不同难度题目的正确率#

SELECT difficult_level,COUNT(IF(result='right',1,NULL))/COUNT(qpd.question_id) correct_rate FROM question_detail qd, question_practice_detail qpd,use...

Mysql

2021-09-28

1 468

题解 | #统计复旦用户8月练题情况#

SELECT u.device_id,university,COUNT(question_id) qustion_cnt, COUNT(if(result='right',1,NULL)) right_question_cnt FROM user_profile u LEFT OUTER JOIN ...

Mysql

2021-09-28

0 600

题解 | #统计每种性别的人数#

转自https://www.cnblogs.com/mqxs/p/7380933.html （1）SUBSTRING_INDEX的写法 SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number FROM user_...

Mysql

2021-09-28

639 11183

题解 | #计算用户的平均次日留存率#

SELECT count(b.device_id)/count(a.device_id) AS avg_ret FROM (SELECT DISTINCT device_id,date FROM question_practice_detail) a LEFT JOIN (SELECT DISTIN...

Mysql

2021-09-26

7 1431

题解 | #计算用户8月每天的练题数量#

SELECT day(date) AS day,count(question_id) AS question_cnt FROM question_practice_detail WHERE year(date)=2021 and month(date)=08 GROUP BY day 记录一下日期函...

Mysql

2021-09-26

1 457

题解 | #计算25岁以上和以下的用户数量#

（1）用if函数的写法。 SELECT IF(age>=25,"25岁及以上","25岁以下") AS age_cut,count(*) AS number FROM user_profile GROUP BY age_cut; （2）case的写法。 select (case ...

Mysql

2021-09-26

287 7930

题解 | #查找所有员工的last_name和first_name以及对应的dept_name#

SELECT last_name,first_name,dept_name FROM (employees LEFT OUTER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no) LEFT OUTER JOIN departments ON dep...

Mysql

2021-09-21

0 420

题解 | #获取所有非manager的员工emp_no#

方法一，NOT EXISTS（似乎不常用呀） SELECT employees.emp_no FROM employees WHERE not EXISTS(select * from dept_manager where dept_manager.emp_no=em...

Mysql

2021-09-21

1 595

题解 | #查找当前薪水详情以及部门编号dept_no#

SELECT last_name,first_name,dept_no FROM employees LEFT JOIN dept_emp on employees.emp_no=dept_emp.emp_no; SQL里面有左连接（left join)，右连接（right join），内连接（i...

Mysql

2021-09-21

1 449