select name,subject,class_code,graduate_date
from teachers
where year(graduate_date) between 1994 and 1997
and gender='f'
order by graduate_date asc

select name,subject,class_code,qualification
from teachers
where head_teacher is not null
and (fir_degr='北京大学' or fir_degr='清华大学')
order by name asc

select t.name,t.subject,t.class_code,t.qualification
from teachers t
where subject='数学' and qualification='Senior'
order by name asc

select a.mch_nm as asshole_tired,a.trx_cnt,b.mch_nm as darling_tired 
from
(selectmch_nm ,count(1)
trx_cnt from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024)
and usr_id='5201314520'
group by mch_nm
having count(*)>=20) a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in(2023,2024)
and usr_id='5211314521') b
on a.mch_nm=b.mch_nm
 order by 2 desc

select a.mch_nm as asshole_tired,a.trx_cnt,b.mch_nm as darling_tired 
from
(
select mch_nm,count(*) trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520'
and year(trx_time) in (2023,2024)
group by mch_nm
having count(*)>=20) a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id='5211314521'
and year(trx_time) in (2023,2024)) b
on a.mch_nm =b.mch_nm 
order by 2 desc

select a.mch_nm as asshole_tired,a.trx_cnt,b.mch_nm as darling_tired 
from
(
select mch_nm,count(*) trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520'
and year(trx_time) in (2023,2024)
group by mch_nm
having count(*)>20) a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id='5201314520'
and year(trx_time) in (2023,2024)) b
on a.mch_nm =b.mch_nm 
order by 2 desc

selecta.*
from 
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id='5201314520' and year(trx_time)=2024) a
join 
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id='5211314521' and year(trx_time)=2024) b 
on a.mch_nm =b.mch_nm
order by 1 desc

select mch_nm 
from cmb_usr_trx_rcd
where usr_id='5201314520'
or usr_id='5211314521'
and year(trx_time)=2024
group by mch_nm
order by mch_nm desc

select mch_nm 
from cmb_usr_trx_rcd
where usr_id='5201314520'
and usr_id='5211314521'
and year(trx_time)=2024
group by mch_nm
order by mch_nm desc

select mch_nm 
from cmb_usr_trx_rcd
where usr_id='5201314520'
and year(trx_time)=2024
group by mch_nm

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 mch_nm='红玫瑰按摩保健休闲'
 and ((year(trx_time) =2023and month(trx_time) between 1 and 12)
or (year(trx_time)=2024and month(trx_time) between 1 and 6))
 group by trx_amt
 order by avg_trx_per_user desc
 limit 5

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 mch_nm='红玫瑰按摩保健休闲'
 and ((year(trx_time) =2023and month(trx_time) between 1 and 12)
or year(trx_time=2024and month(trx_time) between 1 and 6))
 group by trx_amt
 order by avg_trx_per_user desc
 limit 5

select trx_amt,
count(*) as total_trx_cnt,
count(distinct(usr_id)) as unique_usr_cnt,
count(*) /count(distinct usr_id) as avg_trx_per_user
 from cmb_usr_trx_rcd
 where mch_nm='红玫瑰按摩保健休闲'
 and date(trx_time) between '2023-01-01' and '2023-12-31'
or date(trx_time) between '2024-01-01' and '2023-06-30'
 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 mch_nm='红玫瑰按摩保健休闲'
and date(trx_time) between '2024-01-01' and '2024-07-30'
group by trx_amt
order by trx_cnt desc
limit 5

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

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

select trx_amt,count(1) as trx_cnt
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲'
and date(trx_time) between '2024-01-01' and '2024-07-01'
group by trx_amt
order by trx_cnt desc

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 mch_nm='红玫瑰按摩保健休闲'
and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by trx_date

select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where year(trx_time)='2024'
and usr_id='5201314520'
group by mch_nm
order by sum_trx_amt desc

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