全站第 43/1228 名

解决了 32/334 题

中等: 0/75
入门: 11/77
困难: 0/29
简单: 1/114
草履虫: 20/39

过去1年一共提交 61 次

Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul

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

达成1题里程碑2025-02-25
达成2题里程碑2025-02-25
达成5题里程碑2025-02-25
达成10题里程碑2025-03-04
达成20题里程碑2025-03-04
完成w1打卡2025-03-04
完成w2打卡2025-03-04
完成w3打卡2025-03-04
完成w12打卡2025-02-25

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-03-05 登录天数分布  
select * from user_login_log limit 5;
2025-03-05 找出与X轴交点小于等于0的一元一次函数  
SELECT *
FROM numbers_for_fun
WHERE a = 0
AND b != 0
AND (c / b) >= 0
ORDER BY id ASC;
2025-03-04 小结-行转列，展开学生成绩（1）  
SELECT 
    exam_date,
    truncate(avg(CASE WHEN subject = '语文' THEN score ELSE NULL END),0) AS chinese_score,
    truncate(AVG(CASE WHEN subject = '数学' THEN score ELSE NULL END),0) AS math_score,
    truncate(AVG(CASE WHEN subject = '英语' THEN score ELSE NULL END),0) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;
2025-03-04 小结-行转列，展开学生成绩（1）  
SELECT 
    exam_date,
    avg(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    AVG(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    AVG(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;
2025-03-04 小结-行转列，展开学生成绩（1）  
SELECT 
    exam_date,
    MIN(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    MIN(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    MIN(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;
2025-03-04 小结-行转列，展开学生成绩（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;
2025-03-04 HAVING-语数英优异的学生  
select student_id,sum(score)
from scores
where exam_date = '2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having sum(score) > 330
order by student_id
2025-03-04 HAVING-语数英优异的学生  
select student_id,sum(score),exam_date
from scores
where exam_date = '2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having sum(score) > 330
order by student_id
2025-03-04 CASE WHEN-老中青教师数量  
select
	case 
	whenyear(enter_date) >= 2010 then '青年教师'
	whenyear(enter_date) < 2000 then '资深教师'
	else '中年教师'
end as '职称',
count(*) as '数量'
from teachers
group by 职称
2025-03-04 CASE WHEN-老中青教师数量  
select
	case 
	whenyear(enter_date) >= 2010 then '青年教师'
	whenyear(enter_date) < 2000 then '资深教师'
	else '中年教师'
end as teacher_type,
count(*) as '数量'
from teachers
group by teacher_type
2025-03-04 CASE WHEN-男女学生的数量  
select 
	case 
when gender = 'm' then'男'
when gender = 'f' then'女'
		else'other'
	end as gender_text,
count(*) as student_count
from students
group by gender
2025-03-04 CASE WHEN-男女学生的数量  
select 
		case 
when gender = 'm' then '男'
when gender = 'f' then '女'
else 'other'
end as '性别'
from students
2025-03-04 分类（1）姿势太多很过分，分类要用CaseWhen  
select 
	case 
	when trx_amt=288 then '1.WithHand'
when trx_amt=388 then '2.WithMimi'
when trx_amt=588 then '3.BlowJobbie'
when trx_amt=888 then '4.Doi'
when trx_amt=1288 then '5.DoubleFly'
else '6.other'
	end as ser_typ , count(*) as trx_cnt , min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲' and usr_id = '5201314520' 
group by ser_typ
order by ser_typ
2025-03-04 找出与X轴交点大于0的一元一次函数  
SELECT *
FROM numbers_for_fun
WHERE a = 0 AND (c * b) < 0
ORDER BY id ASC;
2025-03-04 找出所有经过原点的一元一次函数  
select * from numbers_for_fun
where (a = '0' and c = '0') and (b < '0' or b > '0')
order by id
2025-03-04 找出所有经过原点的一元一次函数  
select * from numbers_for_fun
where a = '0' and c = '0' and b < '0' or b > '0'
order by id
2025-03-04 找出所有经过原点的一元一次函数  
select * from numbers_for_fun
where a = '0' and c = '0'
order by id DESC
2025-03-04 找出所有经过原点的一元一次函数  
select * from numbers_for_fun
where a = '0' and c = '0'
order by id
2025-03-04 找出所有一元一次函数  
SELECT * 
FROM numbers_for_fun
WHERE a = 0 AND b != 0
ORDER BY id ASC;
2025-03-04 输出特定日期上市的银行  
select * from stock_info
where list_date between '2006-06-01' and '2006-09-01' and industry = '银行'