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

select city,sum(case when con like '%雪%' then 1 else 0 end) as snow_days from weather_rcd_china
group by city

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

select cust_uid,mch_nm from mt_trx_rcd1
where cust_uid='MT10000'
group by mch_nm

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

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

select subject, count(subject) as num from teachers
group by subject 
having num>=3

select class_code, min(birth_date) as oldest from students
group by class_code
order by class_code

select subject, max(score) as top, min(score) as bottom from scores
group by subject

select subject, avg(score) as avg from scores
where exam_date='2024-06-30'
group by subject

select subject, avg(score) as avg from scores
group by subject

select class_code,count(student_id) as student_amt from students
group by class_code

select city, count(con) as cnt,concat(round(count(con)/365*100,2),'%') as p
from weather_rcd_china
where year(dt)=2021 and con like '%多云%'
group by city

select city, count(con) as cnt,count(con)/365 as p
from weather_rcd_china
where year(dt)=2021 and con like '%多云%'
group by city

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

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

select case when trx_amt=288 then '1.withand'
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(3)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

select case when trx_amt=288 then '1.withand'
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(3)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

select case when trx_amt=288 then '1.withand'
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(3)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

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