select student_id,max(score),min(score),avg(score) from scores group by student_id having min(score)>=80 order by student_id

select student_id,max(score),min(score),avg(score) from scores where score>80 group by exam_date,student_id order by student_id

select class_code,min(birth_date) from students group by class_code order by class_code

select subject,max(score),min(score) from scores group by subject order by subject

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

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

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

select
	city,
	count(case when con like '%多云%' then 1 end) as cloudy_days,
	concat(round(
		count(case when con like '%多云%' then 1 end) / count(con),
		2)*100.0,'%'
	) as p
from
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	cloudy_days desc

select
	city,
	count(case when con like '%多云%' then 1 end) as cloudy_days,
	concat(round(
		count(case when con like '%多云%' then 1 end) / count(con),
		2),'%'
	)*100.0 as p
from
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	cloudy_days desc

select
	city,
	count(case when con like '%多云%' then 1 end) as cloudy_days,
	concat(round(
		count(case when con like '%多云%' then 1 end) * 100.0 / count(con),
		2),'%'
	) as p
from
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	cloudy_days desc

select
	city,
	count(case when con like '%多云%' then 1 end) as cloudy_days,
	round(
		count(case when con like '%多云%' then 1 end) * 1.0 / count(con),
		2
	) as p
from
	weather_rcd_china
where
	year(dt) = 2021
group by
	city
order by 
	cloudy_days desc

select
	city,
	count(case when con like '%多云%' then 1 end) as cloudy_days,
	round(
		count(case when con like '%多云%' then 1 end) * 1.0 / count(con),
		2
	) as p
from
	weather_rcd_china
where
	year(dt) = 2021
group by
	city;

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

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

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

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

select
	case
		when trx_amt = 288 then 'WithHand'
		when trx_amt = 388 then 'WithMimi'
		when trx_amt = 588 then 'BlowJobbie'
		when trx_amt = 888 then 'Doi'
		when trx_amt = 1288 then 'DoubleFly'
		else '不知名项目'
	end as ser_typ,
		count(trx_amt) as trx_cnt,
		min(trx_time) as first_date
from
		cmb_usr_trx_rcd
where
		usr_id = '5201314520'
	and mch_nm = '红玫瑰按摩保健休闲'
group by
		case
			when trx_amt = 288 then 'WithHand'
when trx_amt = 388 then 'WithMimi'
when trx_amt = 588 then 'BlowJobbie'
when trx_amt = 888 then 'Doi'
when trx_amt = 1288 then 'DoubleFly'
		else '不知名项目'
	end

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

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

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