select 
distinct 
cust_uid,
mch_nm 
from mt_trx_rcd1 
where cust_uid = 'MT10000'
order by mch_nm;

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

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

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

select subject,max(score) as max_score,min(score)as min_score from scores
group by subject
;

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

select class_code,count(1) as student_count from students
group by class_code
;

select mch_nm,sum(trx_amt) as mch_total_amt from cmb_usr_trx_rcd 
group by mch_nm
HAVING sum(trx_amt)>(
	select avg(trx_amt)*10 from cmb_usr_trx_rcd 
)

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

select * from cmb_usr_trx_rcd
where mch_nm='肯德基'

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

select *,(SELECT AVG(trx_amt) FROM cmb_usr_trx_rcd)as avg_trx_amt
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 platform_avg_amt
from cmb_usr_trx_rcd
GROUP BY usr_id;

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(1) as trx_cnt
,min(date(trx_time)) as first_date
from 
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by trx_cnt desc

select usr_id,mch_nm,sum(trx_amt) as trx_amt,count(1) 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
;

select *
from hand_permutations
where 
    concat(card1, card2) like 'A%A%' or
    concat(card1, card2) like 'A%K%' or
    concat(card1, card2) like 'K%K%' or
    concat(card1, card2) like 'K%A%'
order by id;

select * from weather_rcd_china limit 5;