排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-03-05 至少两门科目大于等于110分的学生  
select 
* 
from subject_score 
where (chinese >=110 and math >=110) or (chinese >=110 and english >=110) or(math >=110 and english >=110)
order by student_id asc;
SELECT student_id, chinese, math, english
FROM (
  SELECT *,
         (chinese >= 110) + (math >= 110) + (english >= 110) AS total
  FROM subject_score
) AS t1
WHERE total >= 2;
SELECT student_id, chinese, math, english
FROM subject_score
HAVING (chinese >= 110) + (math >= 110) + (english >= 110) >= 2;
 
啥也没说
2025-03-04 小结-行转列，展开学生成绩（1）  
这题用SUM也可以输出同样的结果，参考答案用MAX而不是SUM是因为考虑到如果数据存在重复数据需要处理，同个考试可能会有多次成绩的可能性吗
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-03-07 A和K之间的手牌（3）  
select * from hand_permutations 
where card1 between 'A' and 'K' and card2 between 'A' and 'K'
2025-03-07 A和K之间的手牌（2）  
select * from hand_permutations
where card1 between 'A' and 'K' or card2 between 'A' and 'K'
2025-03-07 A和K之间的手牌（1）  
select 
	* 
from hand_permutations 
where card1 between 'A' and 'K'
order by id
2025-03-07 交易金额在5000至10000（含边界）的所有交易  
select * from cmb_usr_trx_rcd 
where trx_amt between 5000 and 10000
order by trx_amt desc
limit 5
2025-03-07 查询播放量为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-03-07 用户听歌习惯的时间分布  
select 
	user_id
	,dayname(start_time) as day_of_week
,count(1) as listens_per_day
from listen_rcd
group by 1,2
order by 1,2
2025-03-07 特定歌曲的播放记录  
select * from listen_rcd
where date(start_time) between '2023-12-10' and '2023-12-31' and song_id = 13
order by start_time
2025-03-07 海王发红包  
SELECT 
snd_usr_id
FROM tx_red_pkt_rcd
WHERE pkt_amt IN (520, 200)
GROUP BY snd_usr_id
HAVING COUNT(pkt_amt) >= 5
order by snd_usr_id asc
2025-03-07 接收红包金额绿茶榜  
select 
	rcv_usr_id
,sum(pkt_amt) sum_trx_amt
from tx_red_pkt_rcd
where rcv_datetime != '1900-01-01 00:00:00'
group by 1
order by 2 desc
limit 10
2025-03-07 接收红包金额绿茶榜  
select 
	snd_usr_id
,sum(pkt_amt) sum_trx_amt
from tx_red_pkt_rcd
group by 1
order by 2 desc
limit 10
2025-03-07 红包金额土豪榜  
select 
	snd_usr_id
,sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd 
group by 1
order by 2 desc
limit 10
2025-03-05 至少两门科目大于等于110分的学生  
SELECT student_id, chinese, math, english
FROM subject_score
HAVING (chinese >= 110) + (math >= 110) + (english >= 110) >= 2;
2025-03-05 交易金额大于10000元的所有交易  
select * from cmb_usr_trx_rcd 
where trx_amt > 100000
order by trx_amt desc
2025-03-05 曝光量最大的商品  
select 
	a.prd_id
,a.prd_nm
,count(distinct b.cust_uid)
from tb_prd_map a
left join tb_pg_act_rcd b on a.prd_id = b.prd_id
where b.if_snd = 1
group by 1,2
order by 3 desc
limit 1
2025-03-05 统计每个用户使用过的不同车型数量  
select 
	cust_uid
,count(distinct car_cls) as unique_car_classes
from didi_sht_rcd 
group by 1
order by 2 desc
2025-03-05 查询所有终点是餐饮类地点的行程记录  
select 
	a.* 
from didi_sht_rcd a
left join loc_nm_ctg b on a.end_loc = b.loc_nm
where b.loc_ctg = '餐饮'
order by a.start_tm asc
2025-03-05 按照车类统计行程次数  
select 
	car_cls
,count(1) as trip_count
from didi_sht_rcd 
group by 1
order by 2 desc
2025-03-05 查询所有起点或终点为“海底捞西丽店”的行程记录  
select 
* 
from didi_sht_rcd 
where start_loc = '海底捞西丽店' or end_loc = '海底捞西丽店'
order by start_tm asc
2025-03-05 登录天数分布  
WITH distinct_login_days AS (
    SELECT 
        usr_id,
        COUNT(DISTINCT DATE(login_time)) AS login_days
    FROM 
        user_login_log
	WHERE 
        login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
    GROUP BY 
        usr_id
)
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
FROM 
    distinct_login_days;
2025-03-04 通勤、午休、临睡个时间段活跃人数分布  
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');