select
teachers.name,
teachers.subject,
teachers.class_code,
teachers.qualification
from teachers 
where
(teachers.fir_degr like '北京大学'
or teachers.fir_degr like '清华大学') 
AND head_teacher IS NOT NULL
order by teachers.name asc

select
teachers.name,
teachers.subject,
teachers.class_code,
teachers.qualification
from teachers 
where
teachers.fir_degr like '北京大学'
or teachers.fir_degr like '清华大学' 
AND head_teacher IS NOT NULL
order by teachers.name asc

select
teachers.name,
teachers.subject,
teachers.class_code,
teachers.qualification
from teachers 
where
teachers.fir_degr like '北京大学'
or teachers.fir_degr like '清华大学' 
order by teachers.name asc

select 
teachers.name,
teachers.subject,
teachers.class_code, 
teachers.qualification
from teachers 
where
teachers.qualification is not null
and teachers.subject = '数学'
order by teachers.name asc

select 
student_id,
name,
birth_date
from students 
where
gender = 'f' 
and birth_date BETWEEN '2009-01-01' AND '2009-12-31'
order by birth_date asc

SELECT 
    ts_code, 
    open_price, 
    close_price
FROM 
    daily_stock_prices
WHERE 
    trade_date = '2023-10-09' AND pct_change > 1;

select 
goods_id,
sum(order_gmv)as total_gmv
from order_info
where
date(order_time)='2024-09-10'
group by goods_id
order by total_gmv desc 
limit 10 ;

select 
count(distinct mch_nm) as mch_cnt
from 
cmb_usr_trx_rcd 
where
mch_nm like '%按摩%'

select 
count(distinct mch_nm) as mch_cnt
from 
cmb_usr_trx_rcd 
where
mch_nm like '%按摩保健休闲%'

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 ser_typ

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

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

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

select 
usr_id,
min(trx_time) as first_time,
mch_nm
from cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
 and usr_id = '5201314520'

select 
trx_amt,
count(trx_amt) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(trx_amt) / count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where
date(trx_time)between'2023-01-01'and'2024-06-30' and
mch_nm = '红玫瑰按摩保健休闲'
group bytrx_amt 
order byavg_trx_per_user desc
limit 5;

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

select 
sum(trx_amt),
max(trx_amt),
min(trx_amt),
avg(trx_amt),
 date(trx_time) as trx_date
from cmb_usr_trx_rcd 
where
date(trx_time)between'2024-09-01'and'2024-09-30'
and mch_nm = '红玫瑰按摩保健休闲'
group by date(trx_time)
order by
trx_date

select 
sum(trx_amt),
max(trx_amt),
min(trx_amt),
avg(trx_amt),
 date(trx_time) as trx_date
from cmb_usr_trx_rcd 
where
date(trx_time)between'2024-09-01'and'2024-09-30'
and mch_nm = '红玫瑰按摩保健休闲'
group by date(trx_time)

select 
sum(trx_amt),
max(trx_amt),
min(trx_amt),
avg(trx_amt)
from cmb_usr_trx_rcd 
where
date(trx_time)between'2024-09-01'and'2024-09-30'
and mch_nm = '红玫瑰按摩保健休闲'
group by date(trx_time)

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