select 
	case 
	when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by 
	reg_rules
order by 
	2;

select 
	case 
	when mch_nm like '按摩保健休闲' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by 
	reg_rules
order by 
	2;

select count(distinct(char(mch_nm) = '%按摩保健休闲%')) as mch_cnt
from cmb_usr_trx_rcd
order by 
	1 ;

select count(distinct(char(mch_nm) = '按摩保健休闲')) as mch_cnt
from cmb_usr_trx_rcd
order by 
	1 ;

select count(char(mch_nm) = '按摩保健休闲') as mch_cnt
from cmb_usr_trx_rcd
order by 
	1 ;

select
	*
from 
	cmb_usr_trx_rcd
where
	date(trx_time)
	between '2024-09-01' and '2024-09-30'
and (
	(hour(trx_time) >= 22) 
or 
	(hour(trx_time) between 0 and 5)
)
 	and usr_id = '5201314520'
order by
	trx_time

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

select *
from cmb_usr_trx_rcd
where usr_id = '5201314520'
 AND (date(trx_time) between '2024-09-01' and '2024-09-30')
 AND hour(trx_time) between 1 and 5
ORDER BY 
	3 ;

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

select 
	subject,
AVG(score) as average_score
from 
	scores
where 
	date(exam_date) = '2024-06-30'
group by 
	subject
order by 
	2;

select 
	subject,
AVG(score) as average_score
from 
	scores
group by 
	subject
order by 
	2;

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

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

select 
	city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(
cast(
sum(case when con like '%多云%' then 1 else 0 end)/ count(1) * 100 as decimal(10, 2)
)
, "%"
)
as p
from 
	weather_rcd_china
where
	year(dt) = 2021
group by 
	city
order by 
	3 desc;

select city,
	sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like "%多云%" then 1 else 0 end)/count(1)*100 as decimal(10, 2)), "%") as 	p
from 
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	3

select city,
	sum(case when con like "多云%" then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like "多云%" then 1 else 0 end)/count(1)*100 as decimal(10, 2)), "%") as 	p
from 
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	3

select city,
	sum(case when con like "多云" then 1 else 0 end) as cloudy_days
,concat(cast(sum(case when con like "多云" then 1 else 0 end)/count(1)*100 as decimal(10, 2)), "%") as 	p
from 
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	3

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 
	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.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 
	 1
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.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 
	1