排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-12-24 登录天数分布  
SELECT 
    SUM(login_days BETWEEN 1 AND 5) AS days_1_to_5,
    SUM(login_days BETWEEN 6 AND 10) AS days_6_to_10,
    SUM(login_days BETWEEN 11 AND 20) AS days_11_to_20,
    SUM(login_days > 20) AS days_over_20
FROM (
    SELECT 
        usr_id,
        COUNT(DISTINCT DATE(login_time)) AS login_days
    FROM 
        user_login_log
    WHERE 
        login_time >= CURDATE() - INTERVAL 180 day
    GROUP BY 
        usr_id
) AS u;  
啥也没说
2025-12-24 用户听歌习惯的时间分布  
select qu.user_id,count(*),dayname(start_time)as day_week from qqmusic_user_info qu
join listen_rcd lr
on qu.user_id=lr.user_id
group by user_id,day_week
order by 1,3
这是哪里不对
 
啥也没说
2025-12-23 文科潜力股  
select * from scores 

where  

(
  (subject='历史' and score>=90) 
  
  or (subject='政治' and score>90) 

  or (subject='地理' and score>=90)
)

and exam_date='2024-06-30'

order by score desc,student_id,subject;
这里是哪里错误
 
啥也没说
2025-12-23 给英语成绩中上水平的学生拔尖  
select * from scores
where exam_date='2024-06-30' and subject='英语' and score betwwen 100 and 110
order by score desc
是哪里出错了，显示有错误
 
啥也没说
2025-12-22 HAVING-语数英优异的学生  
select student_id,sum(score)as total_score from scores
where exam_date='2024-06-30' and subject in('语文','数学','英语')
group by student_id
having sum(score)>300;
哪里出现错误
 
啥也没说
2025-12-22 HAVING-每次成绩都不低于80分的学生  
select student_id,max(score),min(score),avg(score) from scores 
group by student_id
having min(score)>80 
order by student_id;
 
啥也没说
2025-12-22 字符串函数-查找任教5个班级的所有教师  
select name,subject,class_code from teachers where length(class_code)=20
order by name;
 
啥也没说
2025-12-22 数学成绩分段统计（1）  
with avg_math as(
  select 
	case 
        when sc.score>=110 then '[110,120)'
        when sc.score>=90 then '[90,110)'
        when sc.score>=60 then '[60,90)'
        else '[0,60)'
    end as score_range
from students s join scores sc on s.student_id=sc.student_id
where sc.exam_date='2024-06-30'and sc.subject='数学')
select score_range,count(*) as num_students from avg_math
group by score_range;
这样为什么不行，是哪一步错误了吗
 
啥也没说
2025-12-20 不分类别的最火直播间  
select 
		k2.live_id,
    	k2.live_nm,
    	count(*)  
from 
		ks_live_t1 k1 
join
		ks_live_t2 k2 
using(live_id)
where 
		date_format(k1.enter_time,'%Y-%M-%D')='2021-09-12 23'
GROUP BY
		k1.live_id,k2.live_nm
order by  
		count(*) desc 
limit 5;

这个为什么不行
 
啥也没说
2025-12-17 国庆假期后第一天涨幅高于1%的股票  
select ts_code,open_price,close_price 
from daily_stock_prices where pct_change > 0.01 and trade_date='2023-10-08'
不是这样吗
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-12-24 绘制小时进入人数曲线  
select count(*),hour(enter_time) from ks_live_t1 
group by hour(enter_time)
order by 2 desc
2025-12-24 绘制小时进入人数曲线  
select count(*),hour(enter_time) from ks_live_t1 
group by hour(enter_time)
2025-12-24 小结-行转列，展开学生成绩（1）  
select 
	exam_date,
	max(case when subject='语文' then score else 0 end)as chinese,
max(case when subject='数学' then score else 0 end)as math,
max(case when subject='英语' then score else 0 end)as english
from scores 
where student_id=460093 and subject in ('语文','数学','英语')
group by 1
order by 1;
2025-12-24 小结-行转列，展开学生成绩（1）  
select 
	exam_date,
	max(case when subject='语文' then score else null end)as chinese,
max(case when subject='数学' then score else null end)as math,
max(case when subject='英语' then score else null end)as english
from scores 
where student_id=460093 and subject in ('语文','数学','英语')
group by 1
order by 1;
2025-12-24 登录天数分布  
SELECT 
SUM(login_days BETWEEN 1 AND 5) AS days_1_to_5,
SUM(login_days BETWEEN 6 AND 10) AS days_6_to_10,
SUM(login_days BETWEEN 11 AND 20) AS days_11_to_20,
SUM(login_days > 20) AS days_over_20
FROM (
SELECT 
usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 day
GROUP BY 
usr_id
) AS user;
2025-12-24 登录天数分布  
SELECT 
SUM(login_days BETWEEN 1 AND 5) AS days_1_to_5,
SUM(login_days BETWEEN 6 AND 10) AS days_6_to_10,
SUM(login_days BETWEEN 11 AND 20) AS days_11_to_20,
SUM(login_days > 20) AS days_over_20
FROM (
SELECT 
usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 day
GROUP BY 
usr_id
) AS user_stats;
2025-12-24 登录天数分布  
SELECT 
SUM(login_days BETWEEN 1 AND 5) AS days_1_to_5,
SUM(login_days BETWEEN 6 AND 10) AS days_6_to_10,
SUM(login_days BETWEEN 11 AND 20) AS days_11_to_20,
SUM(login_days > 20) AS days_over_20
FROM (
SELECT 
usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 1/2 year
GROUP BY 
usr_id
) AS user_stats;
2025-12-24 登录天数分布  
SELECT 
SUM(login_days BETWEEN 1 AND 5) AS days_1_to_5,
SUM(login_days BETWEEN 6 AND 10) AS days_6_to_10,
SUM(login_days BETWEEN 11 AND 20) AS days_11_to_20,
SUM(login_days > 20) AS days_over_20
FROM (
SELECT 
usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 DAY
GROUP BY 
usr_id
) AS user_stats;
2025-12-24 登录天数分布  
SELECT 
SUM(login_days BETWEEN 1 AND 5) AS days_1_to_5,
SUM(login_days BETWEEN 6 AND 10) AS days_6_to_10,
SUM(login_days BETWEEN 11 AND 20) AS days_11_to_20,
SUM(login_days > 20) AS days_over_20,
COUNT(*) AS total_users
FROM (
SELECT 
usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 DAY
GROUP BY 
usr_id
) AS user_stats;
2025-12-24 登录天数分布  
WITH user_login_days AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 DAY
GROUP BY usr_id, DATE(login_time)
),
distinct_login_days AS (
SELECT 
usr_id,
COUNT(*) AS login_days
FROM 
user_login_days
GROUP BY 
usr_id
),
login_stats AS (
SELECT 
SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20,
COUNT(*) AS total_users
FROM 
distinct_login_days
)
SELECT 
days_1_to_5,
days_6_to_10,
days_11_to_20,
days_over_20 
FROM 
login_stats;
2025-12-24 登录天数分布  
WITH user_login_days AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 DAY
GROUP BY usr_id, DATE(login_time)
),
distinct_login_days AS (
SELECT 
usr_id,
COUNT(*) AS login_days
FROM 
user_login_days
GROUP BY 
usr_id
),
login_stats AS (
SELECT 
SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
SUM(CASE WHEN login_days BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS days_5_to_10,
SUM(CASE WHEN login_days BETWEEN 10 AND 20 THEN 1 ELSE 0 END) AS days_10_to_20,
SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20,
COUNT(*) AS total_users
FROM 
distinct_login_days
)
SELECT 
days_1_to_5,
days_5_to_10,
days_10_to_20,
days_over_20
FROM 
login_stats;
2025-12-24 登录天数分布  
WITH user_login_days AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM 
user_login_log
WHERE 
login_time >= CURDATE() - INTERVAL 180 DAY
GROUP BY usr_id, DATE(login_time)
),
distinct_login_days AS (
SELECT 
usr_id,
COUNT(*) AS login_days
FROM 
user_login_days
GROUP BY 
usr_id
),
login_stats AS (
SELECT 
SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
SUM(CASE WHEN login_days BETWEEN 21 AND 50 THEN 1 ELSE 0 END) AS days_21_to_50,
SUM(CASE WHEN login_days BETWEEN 51 AND 100 THEN 1 ELSE 0 END) AS days_51_to_100,
SUM(CASE WHEN login_days > 100 THEN 1 ELSE 0 END) AS days_over_100,
COUNT(*) AS total_users,
AVG(login_days) AS avg_login_days,
MAX(login_days) AS max_login_days
FROM 
distinct_login_days
)
SELECT 
days_1_to_5,
days_6_to_10,
days_11_to_20,
days_21_to_50,
days_51_to_100,
days_over_100,
total_users,
avg_login_days,
max_login_days,
ROUND(days_1_to_5 * 100.0 / total_users, 2) AS pct_1_to_5,
ROUND(days_6_to_10 * 100.0 / total_users, 2) AS pct_6_to_10,
ROUND(days_11_to_20 * 100.0 / total_users, 2) AS pct_11_to_20
FROM 
login_stats;
2025-12-24 一线城市历年平均气温  
select
 year(dt) as Y,
	cast(avg(case when city='beijing' then tmp_h else null end)as decimal(4,2))as '北京',
cast(avg(case when city='shanghai' then tmp_h else null end)as decimal(4,2)) as '上海',
cast(avg(case when city='shenzhen' then tmp_h else null end)as decimal(4,2))as '深圳',
cast(avg(case when city='guangzhou' then tmp_h else null end)as decimal(4,2))as '广州'
from weather_rcd_china 
WHERE 1 BETWEEN 2011 AND 2022
group by 1
2025-12-24 查询播放量为0的歌手及其专辑  
SELECT 
    s.singer_id,
    s.singer_name,
    a.album_id,
    a.album_name,
    COUNT(l.id) AS play_count
FROM 
    singer_info s
JOIN 
    album_info a ON s.singer_id = a.singer_id
LEFT JOIN 
    song_info sg ON a.album_id = sg.album_id
LEFT JOIN 
    listen_rcd l ON sg.song_id = l.song_id
GROUP BY 
    s.singer_id, s.singer_name, a.album_id, a.album_name
HAVING 
    play_count = 0;
2025-12-24 查询播放量为0的歌手及其专辑  
select singer_name,album_name from singer_info si
left join album_info ai
on si.singer_id=ai.singer_id
group by singer_name,album_name
having sum(dt)=0
2025-12-24 查询播放量为0的歌手及其专辑  
select singer_name,album_name from singer_info si
left join album_info ai
on si.singer_id=ai.singer_id
group by singer_name,album_name
having count(dt)=0
2025-12-24 用户听歌习惯的时间分布  
SELECT 
    u.user_id,
    DAYNAME(lr.start_time) AS day_of_week,
    COUNT(*) AS listens_per_day
FROM 
    qqmusic_user_info u
JOIN 
    listen_rcd lr ON u.user_id = lr.user_id
GROUP BY 
    1, 2
ORDER BY 
    1 ASC, 2 ASC;
2025-12-24 用户听歌习惯的时间分布  
SELECT 
    u.user_id,
    DAYNAME(lr.start_time) AS day_of_week,
    COUNT(*) AS listens_per_day
FROM 
    qqmusic_user_info u
JOIN 
    listen_rcd lr ON u.user_id = lr.user_id
GROUP BY 
    u.user_id, day_of_week
ORDER BY 
    u.user_id ASC, day_of_week ASC;
2025-12-24 用户听歌习惯的时间分布  
select qu.user_id,count(*),dayname(start_time)as day_week from qqmusic_user_info qu
join listen_rcd lr
on qu.user_id=lr.user_id
group by user_id,day_week
order by 1,3
2025-12-24 用户听歌习惯的时间分布  
select qu.user_id,count(*),dayname(start_time)as day_week from qqmusic_user_info qu
join listen_rcd lr
on qu.user_id=lr.user_id
group by user_id,day_week
order by user_id asc,day_week asc