A泡泡先生

分类

/ 注册

全部文章（共10篇）

题解 | 每个月Top3的周杰伦歌曲

select month, ranking, song_name, play_pv from ( SELECT MONTH (fdate) AS month, song_name, ...

2025-06-07

0 11

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

SELECT up.device_id, up.university, sum(if(qpd.question_id is not null,1,0)) question_cnt, sum(if(qpd.result ='right',1,0)) right_que...

2025-06-04

0 14

题解 | 找出每个学校GPA最低的同学

select device_id, university, gpa from(select device_id ,university ,gpa ,row_number() over (partition by university order by gpa) as r f...

2025-06-04

0 16

题解 | 找出每个学校GPA最低的同学

select device_id ,university ,gpa from user_profile where (university ,gpa) in (select university ,min(gpa)from user_profile group by university) or...

2025-06-04

0 13

题解 | 截取出年龄

select substring_index (substring_index (profile, ',', '3'), ',', '-1') age, count(device_id) number from user_submit group by 1 subs...

2025-06-04

0 11

题解 | 提取博客URL中的用户名

select device_id, substring_index(blog_url,'/','-1') user_name from user_submit 加深印象substring_index(a,b,c),截取字符串函数

2025-06-04

0 11

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

select substring_index(profile,',','-1') gender, count(device_id) number from user_submit group by 1 substring_index('a','b','c')截取字符串的函数a代表截取目标字段的总字段...

2025-06-04

0 14

题解 | 分组过滤练习题

select university, round(avg(question_cnt), 3) as avg_question_cnt, round(avg(answer_cnt), 3) as avg_answer_cnt from user_profile ...

2025-06-03

0 21

题解 | 查看学校名称中含北京的用户

select device_id ,age ,university from user_profile where university like '北京%' 模糊查询 where 查询数据 like ’‘例如查询含a的字段a在任何位置的字段like '%a%'a开头任何字段like'a%'a...

2025-06-03

0 18

题解 | 查找除复旦大学的用户信息

select device_id ,gender ,age ,university from user_profile where '复旦大学' not in (device_id ,gender ,age ,university) where 不要的数据 not in (查询数据）抹除一行

2025-06-03

0 11