牛客479560235号

分类

/ 注册

全部文章（共75篇）

题解 | 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

### 首先，查找出原表最高的工资 WITH m_salary AS ( SELECT max(salary) max_salary FROM salaries ), ### 再找出比最大工资小的次大工资 second_salary AS( SELECT max(salary...

2025-04-28

0 52

题解 | 截至当月的练题情况

WITH ### 每个月用户的 monthly_practices_u AS( SELECT device_id,DATE_format(event_date,'%Y-%m') ym,COUNT(question_id) m_user_cnt FROM que...

2025-04-25

0 52

题解 | 任意两个连续自然月练题次数大于1的用户

### 逻辑先求出用户的答题次数再将当月表跟次月表相连接最后再筛选 WITH monthly_practice AS( SELECT device_id, YEAR(event_date) year, ...

2025-04-24

0 65

题解 | 每个用户的第一有效信息

SELECT device_id, IF(answer_cnt IS NOT NULL,CONCAT('答题数:',answer_cnt),IF(gpa IS NOT NULL,CONCAT('gpa:',gpa),IF(age IS NOT NULL,CONCAT('年龄:',a...

2025-04-23

0 40

题解 | 北京大学或练题数大于2的学生ID

SELECT device_id FROM user_profile WHERE device_id IN( SELECT DISTINCT device_id FROM question_practice_detail GROUP BY device_id HAVI...

2025-04-23

0 61

题解 | 所有用户对高难度题目的刷题数

SELECT a.device_id, IFNULL(b.question_cnt,0) question_cnt FROM user_profile a LEFT JOIN ( SELECT t1.device_id,COUNT(t2.question_id) quest...

2025-04-23

0 57

题解 | 平均绩点大于3.6且总人数大于2的学校里学生情况

WITH university_name AS( SELECT university FROM user_profile GROUP BY university HAVING AVG(gpa)>3.6 AND COUNT(id)>2 ), univ...

2025-04-23

0 44

题解 | 存在绩点大于该校平均绩点时的学生信息

WITH avg_gpa AS( SELECT university,AVG(gpa) avg_g FROM user_profile GROUP BY university HAVING AVG(gpa) >3.6 ) SELECT t1.device_id...

2025-04-22

0 42

题解 | 存在绩点大于该校平均绩点时的学生信息

WITH avg_gpa AS( SELECT university,AVG(gpa) avg_g FROM user_profile GROUP BY university HAVING AVG(gpa) >3.6 ) SELECT t1.device_id...

2025-04-22

0 47

题解 | 最受欢迎的top3课程

SELECT a.cid,a.play_cnt pv,a.time_len FROM( SELECT t1.cid, COUNT(*) play_cnt, SUM(timestampdiff(minute,t2.start_time,t2.en...

2025-04-21

0 62