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,
    COUNT(*) AS num_students
FROM 
    students s
JOIN 
    scores sc ON s.student_id = sc.student_id
WHERE 
    sc.subject = '数学' 
    AND sc.exam_date = '2024-06-30'
GROUP BY 
    score_range
ORDER BY 
    score_range DESC;

select case when score >=110 then '[110, 120)'
when score>=90 and score<110 then '[90, 110)' 
when score>=60 and score<90 then '[60, 90)'
else '[0, 60)'
end as score_range, count(*) as num_students
FROM 
    scores sc where sc.subject='数学' and sc.exam_date='2024-06-30' GROUP BY 
    score_range
ORDER BY 
    score_range DESC;

select case when score >=110 then '[110,120)'
when score>=90 and score<110 then '[90,110)' 
when score>=60 and score<90 then '[60,90)'
else '[0,60)'
end as score_range, count(*) as num_students
FROM 
    scores sc where sc.subject='数学' and sc.exam_date='2024-06-30' GROUP BY 
    score_range
ORDER BY 
    score_range DESC;;

select a.*,b.live_nm from 
(select live_id,count(distinct usr_id) from ks_live_t1 where DATE_FORMAT(enter_time,'%Y-%m-%d %H')='2021-09-12 23' group by live_id) a join 
(select * from ks_live_t2) b on a.live_id=b.live_id order by 2 desc;
;

select b.mch_nm as asshole_tried, b.trx_cnt,a.mch_nm as darling_tried 
from (select distinct mch_nm from cmb_usr_trx_rcd where (year(trx_time) between 2023 and 2024) and usr_id='5211314521') a 
right join
(select distinct mch_nm,count(1) trx_cnt from cmb_usr_trx_rcd where (year(trx_time) between 2023 and 2024) and usr_id='5201314520' group by mch_nm having count(mch_nm)>=20) b
on a.mch_nm = b.mch_nm order by 2 desc;

SELECT cm.* 
FROM (
SELECT distinct mch_nm 
FROM cmb_usr_trx_rcd 
WHERE YEAR(trx_time) = 2024 AND usr_id = '5211314521'
) AS cm
JOIN (
SELECT distinct mch_nm 
FROM cmb_usr_trx_rcd 
WHERE YEAR(trx_time) = 2024 AND usr_id = '5201314520'
) AS c
ON cm.mch_nm = c.mch_nm;

select mch_nm,sum(trx_amt) from cmb_usr_trx_rcd group by mch_nm having SUM(trx_amt) > 10 *(select avg(trx_amt) from cmb_usr_trx_rcd);

SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id NOT IN (
    SELECT DISTINCT usr_id
    FROM cmb_usr_trx_rcd
    WHERE mch_nm = '肯德基'
);

SELECT * FROM cmb_usr_trx_rcd
WHERE usr_id IN (
    SELECT usr_id
    FROM cmb_usr_trx_rcd
    WHERE mch_nm = '肯德基'
)
ORDER BY usr_id, trx_time;

select * from cmb_usr_trx_rcdwhere usr_id in (select usr_id from cmb_usr_trx_rcd where mch_nmlike '%肯德基%');

select * from cmb_usr_trx_rcd c where usr_id in (select usr_id from cmb_usr_trx_rcd where c.mch_nmlike '%肯德基%');

select * from cmb_usr_trx_rcd where trx_amt>(select avg(trx_amt) from cmb_usr_trx_rcd);

select usr_id,
    mch_nm,
    trx_time,
    trx_amt, 
(select avg(trx_amt) from cmb_usr_trx_rcd)
from cmb_usr_trx_rcd where usr_id='5201314520';

select usr_id,
	sum(trx_amt) as total_amt,
(select avg(trx_amt) from cmb_usr_trx_rcd) as avg_amt 
from cmb_usr_trx_rcd 
group by usr_id;

select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when mch_nm rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or mch_nm rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;

select count(distinct mch_nm) from cmb_usr_trx_rcd where mch_nm like '%按摩保健休闲%';

select count(*) from cmb_usr_trx_rcd where mch_nm like '%按摩保健休闲%';

select city, sum(case when con like '%雪%' then 1 else 0 end) as snowdays from weather_rcd_china where month(dt) in (12,1,2) group by city order by 2 desc;

select city,count(1) as snowdays from weather_rcd_china where con like '%雪%' and month(dt) in (12,1,2) group by city order by snowdays desc;

select city,count(1) as snowdays from weather_rcd_china where con like '%雪%' and month(dt) in (12,1,2) group by city;