GuaMiao

分类

/ 注册

全部文章（共92篇）

题解 | #0级用户高难度试卷的平均用时和平均得分#

select uid, round(avg(score), 0) as avg_score, round(avg((case when submit_time IS NULL then duration else TIMESTAMPDIFF(MINUTE, start_time, submit_ti...

2023-08-01

1 379

题解 | #对过长的昵称截取处理#char_length

select uid, (case when char_length(nick_name) <= 13 then nick_name else concat(substring(nick_name,1,10),'...') end) as nick_name from ( selec...

2023-07-31

1 315

题解 | #找出每个学校GPA最低的同学#右连接

select a.device_id, b.university, b.gpa from user_profile a right join ( select university, min(gpa) as gpa from user_profile group by uni...

2023-07-31

1 468

题解 | #截取出年龄#

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

2023-07-31

1 385

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

select device_id, substring_index(blog_url, '/', -1) as user_name from user_submit;

2023-07-31

1 289

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

select gender, count(device_id) as number from ( select device_id, substring_index(profile, ',', -1) as gender from user_submit ) as b group b...

2023-07-31

1 322

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

select DAY(date), count(question_id) as question_cnt from question_practice_detail where date between '2021-08-01' and '2021-08-31' group by date;

2023-07-31

1 300

题解 | #查看不同年龄段的用户明细#

select device_id, gender, '20岁以下' as age_cut from user_profile where age < 20 union all select device_id, gender, '20-24岁' as age_cut from user_pro...

2023-07-31

1 348

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

select '25岁以下' as age_cnt, count(*)-sum(age >= 25) as number from user_profile union all select '25岁及以上' as age_cnt, count(device_id) as number fro...

2023-07-31

1 440

题解 | #查找山东大学或者性别为男生的信息#

select device_id, gender, age, gpa from user_profile where university = '山东大学' union all select device_id, gender, age, gpa from user_profile where ge...

2023-07-31

1 292