select * from cmb_usr_trx_rcd 
where 
left(trx_time,7)='2024-09'
and (hour(trx_time)>=22 or hour(trx_time)<=5 )
and usr_id = 5201314520
order by trx_time

select * from cmb_usr_trx_rcd
where usr_id = 5201314520
and left(trx_time,7) = '2024-09'
and time(trx_time) between '01:00:00' and '06:00:00'
 order by trx_time

select city,sum(if(con like '%雪%',1,0))as snowy_days from weather_rcd_china
where month(dt) in (12,1,2)
group by city
order by snowy_days desc

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

select 
trx_amt,
count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(1)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd 
where date_format(trx_time,'%Y-%m') between '2023-01' and '2024-06' 
and mch_nm='红玫瑰按摩保健休闲'
group by trx_amt
order by avg_trx_per_user desc
limit 5

select trx_amt,count(*) as trx_cnt
from cmb_usr_trx_rcd 
where date_format(trx_time,'%Y-%m') between '2024-01' and '2024-07'
and mch_nm='红玫瑰按摩保健休闲'
group by trx_amt
 order by trx_cnt desc
 limit 5

select * from cmb_usr_trx_rcd 
 where usr_id = 5201314520
 anddate(trx_time) between '2024-09-01'and '2024-09-30'
 order by trx_time

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

select 
date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
 where 
 date_format(trx_time, '%Y-%m')='2024-09'
 and mch_nm='红玫瑰按摩保健休闲' 
 group by date(trx_time)
 order by trx_date

select 
date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
 where 
 date_format(trx_time, '%Y-%m')='2024-09'
 and mch_nm='红玫瑰按摩保健休闲' 
 and usr_id=5201314520
 group by trx_date
 order by trx_date

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,
date(min(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

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(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

select usr_id,mch_nm,sum(trx_amt) as trx_amt,count(*) trx_cnt,min(trx_time) as first_time
from cmb_usr_trx_rcd 
where trx_amt>=288 and usr_id=5201314520
group by 
mch_nm
order by trx_cnt desc

select usr_id,mch_nm,sum(trx_amt) as trx_amt,count(*) trx_cnt,min(trx_time) as first_time
from cmb_usr_trx_rcd 
where trx_amt>=288 and usr_id=5201314520
group by 
mch_nm
order by trx_cnt desc
limit 5

select usr_id,min(trx_time) as first_time,mch_nm from cmb_usr_trx_rcd 
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'

select mch_nm as mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd 
where usr_id=5201314520 and left(trx_time,4)=2024
group by mch_nm

SELECT exam_date,
MAX(IF(`subject`='语文',score,0)) as 'chinese_score',
MAX(IF(`subject`='数学',score,0)) as 'math_score',
MAX(IF(`subject`='英语',score,0)) as 'english_score'
FROM scores 
where student_id = 460093
group by 
exam_date

SELECT exam_date,
MAX(CASE WHEN subject='语文' THEN score ELSE 0 END) as 'chinese_score',
MAX(CASE WHEN subject='数学' THEN score ELSE 0 END) as 'math_score',
MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) as 'english_score'
FROM scores 
where student_id = 460093
group by 
exam_date

select student_id,sum(score) as total_score
from scores 
where
exam_date='2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having
 sum(score)>330

select student_id,sum(score) as total_score
from scores 
where
exam_date='2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having
 sum(score)>=330