细雨噜噜噜

分类

题解(99)

/ 注册

全部文章（共60篇）

题解 | #考试分数(二)#

法1：来自课本 SELECT * FROM grade x WHERE score>(SELECT AVG(score) FROM grade y WHERE x.job=y.job) ORDER BY id; 法2：原表和平均成绩表做连接 SELECT id,grade.job,grad...

Mysql

2021-09-30

0 382

题解 | #查找字符串 10,A,B 中逗号,出现的次数cnt#

mark下length函数统计字符串长度，还有借助replace函数算字符串缩短的长度。 SELECT LENGTH('10,A,B')-LENGTH(REPLACE('10,A,B',",",""))

Mysql

2021-09-29

0 332

题解 | #构造一个触发器audit_log#

记录触发器的写法。begin和end之间写触发的动作，要写分号。 CREATE TRIGGER audit_log AFTER INSERT ON employees_test FOR EACH ROW BEGIN INSERT INTO audit VALUES(NEW.ID,NEW.NAME...

Mysql

2021-09-29

0 320

题解 | #获取每个部门中当前员工薪水最高的相关信息#

注意要点：选薪资最大的，所以(PARTITION BY dept_no ORDER BY salary DESC)要降序。 SELECT dept_no,a.emp_no,salary AS maxSalary FROM(SELECT dept_no,dept_emp.emp_no,salary, ...

Mysql

2021-09-29

1 333

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

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 467

题解 | #统计复旦用户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

635 11182

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

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 1426

题解 | #计算用户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 456