select city,sum(case when con like '%多云%' then 1 else 0 end)as duoyun_num,concat(round(sum(case when con like '%多云%' then 1 else 0 end )/count(*)*100,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 duoyun_num,round(sum(case when con like '%多云%' then 1 else 0 end )/count(*),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 duoyun_num,round(sum(case when con like '%多云%' then 1 else 0 end )/count(*),2) as 'p'
from weather_rcd_china 
where year(dt) = 2021 
group by city

select 
case 
when trx_amt = 1288 then '5.DoubleFly'
when trx_amt = 888 then '4.Doi'
when trx_amt = 588 then '3.BlowJobbie'
when trx_amt = 388 then '2.WithMimi'
when trx_amt = 288 then '1.WithHand'
else '6.other'
end as ser_typ,
count(1) as trx_cnt, substr(min(trx_time),1,10) 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 >= 1288 then '5.DoubleFly'
when trx_amt >= 888 then '4.Doi'
when trx_amt >= 588 then '3.BlowJobbie'
when trx_amt >= 388 then '2.WithMimi'
when trx_amt >= 288 then '1.WithHand'
else 'other'
end as ser_typ,
count(1) as trx_cnt, substr(min(trx_time),1,10) 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 sum_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 usr_id,mch_nm
order by trx_cnt

select mch_nm,sum(trx_amt) as sum_trx_amt 
from cmb_usr_trx_rcd 
where usr_id=5201314520 and substr(trx_time,1,4)= '2024'
group by mch_nm;

select * from numbers_for_fun 
where a != 0 and b*b - 4*a*c = 0
order by id;

select * from numbers_for_fun 
where a != 0 and c = 0
order by id;

select * from numbers_for_fun 
where a > 0 and c= 0
order by id;

select * from numbers_for_fun 
where a =0 and ((3*b+c) = (-8))
order by id;

select * from numbers_for_fun 
where a = 0 and b <0 and c >=0
order by id;

select * from apple_pchs_rcd 
where order_channel = '官网' and payment_method = 'Apple Pay'
and product_price >= 3000
order by order_id;

select * from scores 
where exam_date = '2024-06-30'
and subject in ('历史','政治','地理')
and score >= 90
order by score desc ,student_id,subject;

select * from scores 
where exam_date = '2024-06-30'
and (subject = '英语' and score between 100 and 110)
order by score desc;

select * from students 
where class_code IN ('C219','C220','C221') and gender = 'f'
order by student_id;

select * from hand_permutations
where card1 >'J' and card1 < 'K' 
and card2 >'J' and card2 < 'K'
order by id;

select * from singer_info 
where birth_date < '2000-01-01' and gender = 'm';

select * from singer_info 
where birth_date < '2000-01-01';

select student_id from subject_score 
where (chinese+ math + english)>= 300;