排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2026-03-10 抖音面试真题（1）T+1日留存率  
WITH 
user_login_date AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
GROUP BY usr_id, DATE(login_time)
),
daily_login_count AS (
SELECT
login_date AS t_date,
COUNT(DISTINCT usr_id) AS A
FROM user_login_date
GROUP BY login_date
),
retained_users AS (
SELECT
t1.login_date AS t_date,
COUNT(DISTINCT t1.usr_id) AS B
FROM user_login_date t1
INNER JOIN user_login_date t2
ON t1.usr_id = t2.usr_id
AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
GROUP BY t1.login_date
)
SELECT
d1.t_date as login_date,
CONCAT(ROUND(COALESCE(d2.B / d1.A * 100, 0), 2), '%') AS T1_retention_rate
FROM daily_login_count d1
LEFT JOIN retained_users d2
ON d1.t_date = d2.t_date
WHERE d1.t_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY d1.t_date DESC;
2026-03-08 小结-缺考超过5次的学生  
select student_id
from scores 
group by student_id
having count(case when score is null then 1 end) > 5
order by student_id asc;
2026-03-08 小结-从不缺考的学生  
SELECT student_id
FROM scores
GROUP BY student_id
HAVING COUNT(*) = SUM(CASE WHEN score IS NOT NULL THEN 1 ELSE 0 END)
ORDER  BY  student_id
2026-03-08 小结-从不缺考的学生  
select distinct student_id
from scores
where score is not null
order by student_id asc;
2026-03-08 小结-从不缺考的学生  
select distinct student_id
from scores
where score is not null;
2026-03-08 字符串函数-查找任教3个班级的所有教师  
SELECT name, subject, class_code
FROM teachers
WHERE LENGTH(class_code) - LENGTH(REPLACE(class_code, 'C', '')) = 3
ORDER BY name ASC;
2026-03-08 字符串函数-查找任教5个班级的所有教师  
select 
	name,
subject,
class_code
from teachers
where length(class_code)-length(replace(class_code,'C','')) = 5
order by name asc;
2026-03-06 NULL的知识点  
SELECT 
    COUNT(1) AS rows_num, 
    COUNT(DISTINCT student_id) AS students_num,  
    SUM(CASE WHEN score IS NOT NULL THEN 1 ELSE 0 END) AS score_num, 
    SUM(CASE WHEN score = 0 THEN 1 ELSE 0 END) AS score_zero_num, 
    SUM(CASE WHEN score IS NULL THEN 1 ELSE 0 END) AS score_null_num  
FROM 
    scores;
2026-03-06 数学成绩分段统计（3）  
WITH class_total AS (
SELECT
class_code,
COUNT(DISTINCT student_id) AS total_students 
FROM students
GROUP BY class_code
),
score_range AS (
SELECT
a.class_code,
CASE 
WHEN b.score >= 110 THEN 'excellent'
WHEN b.score >= 90 THEN 'good'
WHEN b.score >= 60 THEN 'pass'
ELSE 'fail'
END AS score_range
FROM students a
LEFT JOIN scores b 
ON a.student_id = b.student_id 
AND b.subject = '数学' 
AND b.exam_date = '2024-06-30'
),
range_num AS (
SELECT 
class_code,
SUM(CASE WHEN score_range = 'excellent' THEN 1 ELSE 0 END) AS excellent_count,
SUM(CASE WHEN score_range = 'good' THEN 1 ELSE 0 END) AS good_count,
SUM(CASE WHEN score_range = 'pass' THEN 1 ELSE 0 END) AS pass_count,
SUM(CASE WHEN score_range = 'fail' THEN 1 ELSE 0 END) AS fail_count
FROM score_range
GROUP BY class_code
)
SELECT
ct.class_code,
ct.total_students,
CONCAT(
rn.excellent_count,
',',
ROUND(IFNULL(rn.excellent_count / NULLIF(ct.total_students, 0), 0) * 100, 2),
'%'
) AS excellent,
CONCAT(
rn.good_count,
',',
ROUND(IFNULL(rn.good_count / NULLIF(ct.total_students, 0), 0) * 100, 2),
'%'
) AS good,
CONCAT(
rn.pass_count,
',',
ROUND(IFNULL(rn.pass_count / NULLIF(ct.total_students, 0), 0) * 100, 2),
'%'
) AS pass,
CONCAT(
rn.fail_count,
',',
ROUND(IFNULL(rn.fail_count / NULLIF(ct.total_students, 0), 0) * 100, 2),
'%'
) AS fail
FROM class_total ct
LEFT JOIN range_num rn ON ct.class_code = rn.class_code 
ORDER BY ct.class_code;
2026-03-06 数学成绩分段统计（2）  
with score_range as(
select
a.class_code,
case 
	when b.score >= 110 then 'excellent'
when b.score >= 90 then 'good'
when b.score >= 60 then 'pass'
when b.score < 60 then 'fail'
end as score_range
from students a
join scores b on a.student_id = b.student_id
where b.subject = '数学' and b.exam_date = '2024-06-30'
)
select 
	class_code,
sum(case when score_range = 'excellent' then 1 else 0 end) as excellent_count,
sum(case when score_range = 'good' then 1 else 0 end) as good_count,
sum(case when score_range = 'pass' then 1 else 0 end) as pass_count,
sum(case when score_range = 'fail' then 1 else 0 end) as fail_count
from score_range
group by class_code
order by class_code
2026-03-06 各班第一名  
WITH student_subject_scores AS (
SELECT
s.student_id,
s.name,
s.class_code,
COALESCE(MAX(CASE WHEN sc.subject = '语文' THEN sc.score END), 0) AS chinese_score,
COALESCE(MAX(CASE WHEN sc.subject = '数学' THEN sc.score END), 0) AS math_score,
COALESCE(MAX(CASE WHEN sc.subject = '英语' THEN sc.score END), 0) AS english_score,
COALESCE(MAX(CASE WHEN sc.subject = '物理' THEN sc.score END), 0) AS physics_score,
COALESCE(MAX(CASE WHEN sc.subject = '化学' THEN sc.score END), 0) AS chemistry_score,
COALESCE(MAX(CASE WHEN sc.subject = '生物' THEN sc.score END), 0) AS biology_score,
COALESCE(MAX(CASE WHEN sc.subject = '地理' THEN sc.score END), 0) AS geography_score,
COALESCE(MAX(CASE WHEN sc.subject = '历史' THEN sc.score END), 0) AS history_score,
COALESCE(MAX(CASE WHEN sc.subject = '政治' THEN sc.score END), 0) AS politics_score,
COALESCE(SUM(sc.score), 0) AS total_score
FROM students s
LEFT JOIN scores sc 
ON s.student_id = sc.student_id 
AND sc.exam_date = '2024-06-30'
GROUP BY s.student_id, s.name, s.class_code
),
class_max_total AS (
SELECT
class_code,
MAX(total_score) AS 班级最高总分
FROM student_subject_scores
GROUP BY class_code
)
SELECT
	sss.class_code,
sss.name,
sss.chinese_score,
sss.math_score,
sss.english_score,
sss.physics_score,
sss.chemistry_score,
sss.biology_score,
sss.geography_score,
sss.history_score,
sss.politics_score,
sss.total_score
FROM student_subject_scores sss
JOIN class_max_total cmt 
ON sss.class_code = cmt.class_code 
AND sss.total_score = cmt.班级最高总分
ORDER BY sss.class_code;
2026-03-06 各班第一名  
WITH student_subject_scores AS (
SELECT
s.student_id,
s.name,
s.class_code,
COALESCE(MAX(CASE WHEN sc.subject = '语文' THEN sc.score END), 0) AS chinese_score,
COALESCE(MAX(CASE WHEN sc.subject = '数学' THEN sc.score END), 0) AS math_score,
COALESCE(MAX(CASE WHEN sc.subject = '英语' THEN sc.score END), 0) AS english_score,
COALESCE(MAX(CASE WHEN sc.subject = '物理' THEN sc.score END), 0) AS physics_score,
COALESCE(MAX(CASE WHEN sc.subject = '化学' THEN sc.score END), 0) AS chemistry_score,
COALESCE(MAX(CASE WHEN sc.subject = '生物' THEN sc.score END), 0) AS biology_score,
COALESCE(MAX(CASE WHEN sc.subject = '地理' THEN sc.score END), 0) AS geography_score,
COALESCE(MAX(CASE WHEN sc.subject = '历史' THEN sc.score END), 0) AS history_score,
COALESCE(MAX(CASE WHEN sc.subject = '政治' THEN sc.score END), 0) AS politics_score,
COALESCE(SUM(sc.score), 0) AS total_score
FROM students s
LEFT JOIN scores sc 
ON s.student_id = sc.student_id 
AND sc.exam_date = '2024-06-30'
GROUP BY s.student_id, s.name, s.class_code
),
class_max_total AS (
SELECT
class_code,
MAX(total_score) AS 班级最高总分
FROM student_subject_scores
GROUP BY class_code
)
SELECT
sss.name,
sss.chinese_score,
sss.math_score,
sss.english_score,
sss.physics_score,
sss.chemistry_score,
sss.biology_score,
sss.geography_score,
sss.history_score,
sss.politics_score,
sss.total_score
FROM student_subject_scores sss
JOIN class_max_total cmt 
ON sss.class_code = cmt.class_code 
AND sss.total_score = cmt.班级最高总分
ORDER BY sss.class_code;
2026-03-06 各班第一名  
WITH student_subject_scores AS (
SELECT
s.student_id,
s.name,
s.class_code,
COALESCE(MAX(CASE WHEN sc.subject = '语文' THEN sc.score END), 0) AS 语文,
COALESCE(MAX(CASE WHEN sc.subject = '数学' THEN sc.score END), 0) AS 数学,
COALESCE(MAX(CASE WHEN sc.subject = '英语' THEN sc.score END), 0) AS 英语,
COALESCE(MAX(CASE WHEN sc.subject = '物理' THEN sc.score END), 0) AS 物理,
COALESCE(MAX(CASE WHEN sc.subject = '化学' THEN sc.score END), 0) AS 化学,
COALESCE(MAX(CASE WHEN sc.subject = '生物' THEN sc.score END), 0) AS 生物,
COALESCE(MAX(CASE WHEN sc.subject = '地理' THEN sc.score END), 0) AS 地理,
COALESCE(MAX(CASE WHEN sc.subject = '历史' THEN sc.score END), 0) AS 历史,
COALESCE(MAX(CASE WHEN sc.subject = '政治' THEN sc.score END), 0) AS 政治,
COALESCE(SUM(sc.score), 0) AS 总成绩
FROM students s
LEFT JOIN scores sc 
ON s.student_id = sc.student_id 
AND sc.exam_date = '2024-06-30'
GROUP BY s.student_id, s.name, s.class_code
),
class_max_total AS (
SELECT
class_code,
MAX(总成绩) AS 班级最高总分
FROM student_subject_scores
GROUP BY class_code
)
SELECT
sss.name,
sss.语文,
sss.数学,
sss.英语,
sss.物理,
sss.化学,
sss.生物,
sss.地理,
sss.历史,
sss.政治,
sss.总成绩
FROM student_subject_scores sss
JOIN class_max_total cmt 
ON sss.class_code = cmt.class_code 
AND sss.总成绩 = cmt.班级最高总分
ORDER BY sss.class_code;
2026-03-06 文科潜力股  
select *
from scores 
where exam_date = '2024-06-30'
	and subject in ('历史','政治','地理')
and score >= 90
order by score desc ,student_id,subject;
2026-03-06 给英语成绩中上水平的学生拔尖  
select *	
from scores 
where subject = '英语' and score between 100 and 110
	and exam_date = '2024-06-30'
order by score desc;
2026-03-06 找出三个班级的女生  
select *
from students 
where class_code in ('C219','C220','C221')
	and gender = 'f'
order by student_id;
2026-03-06 找出三个班级的女生  
select *
from students 
where class_code in ('C219','C220','C221')
	and gender = 'm'
order by student_id;
2026-03-06 语文数学英语至少1门超过100分的同学  
select 
    * 
from 
    subject_score 
where chinese >100 or math >100 or english >100 
order by chinese
2026-03-06 小结-行转列，展开学生成绩（1）  
SELECT 
    exam_date,
    MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;
2026-03-05 HAVING-语数英优异的学生  
select
	student_id,
sum(score) as total_score
from scores
where subject in ('语文','数学','英语')
	and exam_date = '2024-06-30'
group by student_id
having sum(score) > 330
order by total_score desc;