select distinct cust_uid, mch_nm from mt_trx_rcd1
where cust_uid = 'MT10000'

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

select subject from teachers
group by subject
having count(teacher_id) >= 3

select student_id,
	max(score) as max_score,
min(score) as min_score,
avg(score) as avg_score
from scores
group by student_id
having min(score) >= 80
order by student_id

select student_id from scores
group by student_id
having min(score) >= 80
order by student_id

select student_id from scores
where score >= 80
group by student_id
order by student_id

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

select subject,
	max(score) as highest,
min(score) as lowest
from scores
group by subject
order by subject

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

select class_code,
	count(student_id) as student_count
from students
group by class_code
order by student_count desc

select car_cls,
	count(1) as trip_count
from didi_sht_rcd
group by car_cls
order by trip_count desc

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

select city,
	count(con) as days,
	round(count(con)/365,2) as percent	
from weather_rcd_china
where year(dt) = 2021 and con = '多云'
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(trx_amt) 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.others'
	end as ser_typ,
count(1) 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 1

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.others'
	end as ser_typ,
count(1) 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.others'
	end as ser_typ,
count(trx_amt) 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.others'
	end as ser_typ,
count(trx_amt) 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.others'
	end as ser_typ,
count(trx_amt) 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(mch_nm) 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