排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  已解决
2025-12-26 滴滴面试真题（2）打车订单呼叫应答时间  已解决
2025-12-26 抖音面试真题（1）T+1日留存率  未解决
2025-12-25 绘制小时进入人数曲线  已解决
2025-12-23 一线城市历年平均气温  已解决
2025-12-22 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  已解决
2025-12-22 查询播放量为0的歌手及其专辑  未解决
2025-12-21 小结-行转列，展开学生成绩（1）  已解决
2025-12-21 HAVING-每次成绩都不低于80分的学生  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-12-25 绘制小时进入人数曲线  
这样写不就可以了么
SELECT 
	HOUR(enter_time) AS hour_entered,
	COUNT(usr_id) AS enter_count
FROM ks_live_t1
GROUP BY HOUR(enter_time)
ORDER BY HOUR(enter_time)
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2026-01-05 性别已知的听歌用户  
select * from qqmusic_user_info
where gender in ('f','m')
	and year(birth_date) = 1980
order by birth_date
2026-01-05 2000年以前出生的男歌手  
select * from singer_info
where gender = 'm'
	and year(birth_date) < 2000
2026-01-05 21世纪上市的银行  
select * from stock_info 
where industry = '银行' 
	and YEAR(list_date) BETWEEN 2000 AND 2099
order by list_date
2026-01-05 输出地区为北京的所有银行  
select * from stock_info 
where area = '北京' and industry = '银行'
order by list_date
2026-01-05 输出地区为北京的所有银行  
select * from stock_info 
where area = '北京'
order by list_date
2026-01-05 1989年12月13日出生的女歌手  
select * from singer_info 
where birth_date = '1989-12-13' and gender = 'f'
2026-01-05 找出所有港台歌手  
select * from singer_info 
where type2 = '港台'
order by singer_id
2026-01-05 找出所有港台乐队  
select * from singer_info 
where type2 = '港台' and type3 = '乐队'
order by 'type2'
2026-01-05 找出所有港台乐队  
select * from singer_info 
where type2 = '港台'
order by 'type2'
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00'
             OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00' THEN usr_id
    END) AS commute,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN usr_id
    END) AS lunch_break,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id
    END) AS bedtime
FROM
    user_login_log
WHERE
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT 
SUM(DISTINCT CASE 
WHEN (TIME(login_time) BETWEEN '07:30:00' AND '09:30:00')
 OR (TIME(login_time) BETWEEN '18:30:00' AND '20:30:00') THEN 1 ELSE 0 END) AS commute,
SUM(DISTINCT CASE 
WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN 1 ELSE 0 END) AS lunch_break, 
SUM(DISTINCT CASE 
WHEN (TIME(login_time) BETWEEN '22:30:00' AND '23：59:59') 
OR (TIME(login_time) BETWEEN '00:00:00' AND '01:00:00') THEN 1 ELSE 0 END) AS bedtime 
FROM user_login_log
WHERElogin_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT 
SUM(DISTINCT CASE WHEN (TIME(login_time) BETWEEN '07:30:00' AND '09:30:00')
 OR (TIME(login_time) BETWEEN '18:30:00' AND '20:30:00') THEN 1 ELSE 0 END) AS commute,
SUM(DISTINCT CASE WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN 1 ELSE 0 END) AS lunch_break, 
SUM(DISTINCT CASE WHEN (TIME(login_time) BETWEEN '22:30:00' AND '23：59:59') 
OR (TIME(login_time) BETWEEN '00:00:00' AND '01:00:00') THEN 1 ELSE 0 END) AS bedtime 
FROM user_login_log
WHERElogin_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT 
SUM(DISTINCT CASE WHEN (TIME(login_time) BETWEEN '07:30:00' AND '09:30:00')
 OR (TIME(login_time) BETWEEN '18:30:00' AND '20:30:00') THEN 1 ELSE 0 END) AS commute,
SUM(DISTINCT CASE WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN 1 ELSE 0 END) AS lunch_break, 
SUM(DISTINCT CASE WHEN (TIME(login_time) BETWEEN '22:30:00' AND '23：59:59') 
OR (TIME(login_time) BETWEEN '00:00:00' AND '01:00:00') THEN 1 ELSE 0 END) AS bedtime 
FROM user_login_log
WHEREMONTH(login_time) = MONTH(CURRENT_DATE)-1
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT 
SUM(DISTINCT CASE WHEN (TIME(login_time) BETWEEN '07:30:00' AND '09:30:00')
 OR (TIME(login_time) BETWEEN '18:30:00' AND '20:30:00') THEN 1 ELSE 0 END) AS commute,
SUM(DISTINCT CASE WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN 1 ELSE 0 END) AS lunch_break, 
SUM(DISTINCT CASE WHEN TIME(login_time) BETWEEN '22:30:00' AND '01:00:00' THEN 1 ELSE 0 END) AS bedtime 
FROM user_login_log
WHEREMONTH(login_time) = MONTH(CURRENT_DATE)-1
2026-01-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT 
SUM(CASE WHEN (TIME(login_time) BETWEEN '07:30:00' AND '09:30:00')
 OR (TIME(login_time) BETWEEN '18:30:00' AND '20:30:00') THEN 1 ELSE 0 END) AS commute,
SUM(CASE WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN 1 ELSE 0 END) AS lunch_break, 
SUM(CASE WHEN TIME(login_time) BETWEEN '22:30:00' AND '01:00:00' THEN 1 ELSE 0 END) AS bedtime 
FROM user_login_log
WHEREMONTH(login_time) = MONTH(CURRENT_DATE)-1
2026-01-05 国庆假期后第一天涨幅高于1%的股票  
SELECT ts_code, open_price, close_price
FROM daily_stock_prices
WHERE trade_date = '2023-10-09'
	AND pct_change > 1
2026-01-05 国庆假期后第一天涨幅高于1%的股票  
SELECT ts_code, open_price, close_price
FROM daily_stock_prices
WHERE trade_date = '2023-10-09'
	AND pct_change > 0.1
2026-01-04 一线城市历年平均气温  
SELECT 
	YEAR(dt) AS Y,
CAST(AVG(CASE WHEN city = 'beijing' THEN tmp_h END) AS DECIMAL(4,2)) AS 北京,
CAST(AVG(CASE WHEN city = 'shanghai' THEN tmp_h END) AS DECIMAL(4,2)) AS 上海,
CAST(AVG(CASE WHEN city = 'shenzhen' THEN tmp_h END) AS DECIMAL(4,2)) AS 深圳,
CAST(AVG(CASE WHEN city = 'guangzhou' THEN tmp_h END) AS DECIMAL(4,2)) AS 广州
FROM weather_rcd_china
WHERE 
	city IN ('beijing','shanghai','guangzhou','shenzhen')
AND YEAR(dt) BETWEEN 2011 AND 2022
GROUP BY YEAR(dt)
ORDER BY Y
2026-01-04 一线城市历年平均气温  
SELECT 
	YEAR(dt) AS Y,
CAST(ROUND(AVG(CASE WHEN city = 'beijing' THEN tmp_h END),2) AS DECIMAL(4,2)) AS 北京,
CAST(ROUND(AVG(CASE WHEN city = 'shanghai' THEN tmp_h END),2) AS DECIMAL(4,2)) AS 上海,
CAST(ROUND(AVG(CASE WHEN city = 'shenzhen' THEN tmp_h END),2) AS DECIMAL(4,2)) AS 深圳,
CAST(ROUND(AVG(CASE WHEN city = 'guangzhou' THEN tmp_h END),2) AS DECIMAL(4,2)) AS 广州
FROM weather_rcd_china
WHERE 
	city IN ('beijing','shanghai','guangzhou','shenzhen')
AND YEAR(dt) BETWEEN 2011 AND 2022
GROUP BY YEAR(dt)
ORDER BY Y
2026-01-04 一线城市历年平均气温  
SELECT 
	YEAR(dt) AS Y,
CAST(ROUND(AVG(CASE WHEN city = 'beijing' THEN tmp_h END),2) AS DECIMAL(10,2)) AS 北京,
CAST(ROUND(AVG(CASE WHEN city = 'shanghai' THEN tmp_h END),2) AS DECIMAL(10,2)) AS 上海,
CAST(ROUND(AVG(CASE WHEN city = 'shenzhen' THEN tmp_h END),2) AS DECIMAL(10,2)) AS 深圳,
CAST(ROUND(AVG(CASE WHEN city = 'guangzhou' THEN tmp_h END),2) AS DECIMAL(10,2)) AS 广州
FROM weather_rcd_china
WHERE 
	city IN ('beijing','shanghai','guangzhou','shenzhen')
AND YEAR(dt) BETWEEN 2011 AND 2022
GROUP BY YEAR(dt)
ORDER BY Y