细雨噜噜噜
细雨噜噜噜
全部文章
分类
题解(99)
归档
标签
去牛客网
登录
/
注册
细雨噜噜噜的博客
全部文章
(共122篇)
题解 | #计算用户的平均次日留存率#
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
5
1057
题解 | #计算用户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
375
题解 | #计算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
211
7030
题解 | #查找所有员工的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
331
题解 | #获取所有非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
504
题解 | #查找当前薪水详情以及部门编号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
359
题解 | #找出每个学校GPA最低的同学#
SELECT device_id,university,min(gpa) FROM user_profile GROUP BY university; 因为学校与学生是一对多的关系,如果仅用min求出gpa最低的学生,查询结果中的id与学生不一定是对应的关系,因此此方法错误。 (1)方法一:将表a的...
Mysql
2021-09-19
129
5864
题解 | #查找山东大学男生的GPA#
SELECT device_id,gender,age,gpa FROM user_profile WHERE university='山东大学' UNION ALL SELECT device_id,gender,age,gpa FROM user_profile WHERE gender=&q...
Mysql
2021-09-19
95
2047
题解 | #统计每个学校的用户平均答题数#
SELECT university,COUNT(question_practice_detail.question_id)/COUNT(distinct(user_profile.device_id)) FROM user_profile,question_practice_detail WHERE...
Mysql
2021-09-19
0
308
题解 | #分组排序练习题#
SELECT university,AVG(question_cnt) FROM user_profile GROUP by university ORDER BY AVG(question_cnt) ASC; 按照平均发帖升序。或者下面这样写法好点 SELECT university,AVG(q...
Mysql
2021-09-19
1
296
首页
上一页
4
5
6
7
8
9
10
11
12
13
下一页
末页