select
		k2.live_id,
k2.live_nm,
count(k1.usr_id) as num
from
		ks_live_t1 k1
inner join
		ks_live_t2 k2
on
		k1.live_id = k2.live_id
where
		date_format(k1.enter_time,'%Y-%M-%D %H') = '2021-09-12 23'
group by
		k2.live_id, k2.live_nm
order by
		num desc
limit 5;

select
		k2.live_id,
k2.live_nm,
(k1.leave_time - k1.enter_time) as time
from
		ks_live_t1 k1
inner join
		ks_live_t2 k2
on
		k1.live_id = k2.live_id
order by
		time desc
limit 5

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

select
		*
from
		scores
where
		subject in ('历史','政治','地理')
and
		score > 90
and
		exam_date = '2024-06-30'
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
		subject_score
where
		(chinese > '100' or math > '100' or english > '100')
order by
		chinese

select
so.exam_date,
max(case when so.subject = '语文' then score
	else null
end)as chinese_score,
max(case when so.subject = '数学' then score
	else null
end)as math_score,
max(case when so.subject = '英语' then score
	else null
end)as english_score
from
		students s 
left join
		scores so
on
		s.student_id = so.student_id
where
		(so.subject = '语文' or so.subject = '数学' or so.subject = '英语')
and
		s.student_id = '460093'
group by
		exam_date

select
		s.student_id,
sum(so.score) as total_score
from
		students s
left join
		scores so
on
		s.student_id = so.student_id
where
		so.exam_date = '2024-06-30'
and
		(so.subject = '语文' or so.subject = '数学' or so.subject = '英语')
group by
		s.student_id
having
		total_score > 330

select
sum(so.score) as total_score
from
		students s
left join
		scores so
on
		s.student_id = so.student_id
where
		so.exam_date = '2024-06-30'
and
		(so.subject = '语文' or so.subject = '数学' or so.subject = '英语')
group by
		s.student_id
having
		total_score > 330

select
		name,
sum(so.score) as total_score
from
		students s
left join
		scores so
on
		s.student_id = so.student_id
where
		so.exam_date = '2024-06-30'
and
		(so.subject = '语文' or so.subject = '数学' or so.subject = '英语')
group by
		s.student_id
having
		total_score > 330

with max_num as(
select
		subject,
count(name) as num
from
		teachers
group by
		subject
having
		num >= 3
)
select
		subject
from
		max_num

select
		subject,
count(name) as num
from
		teachers
group by
		subject
having
		num >= 3

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,
min(score)
from
		scores
group by
		student_id
having
		min(score) >= 80
order by
		student_id

select
		case when year(enter_date) >= '2010' then '青年教师'
	 when year(enter_date) < '2000' then '资深教师'
 else '中年教师'
endas type,
count(name) 
from
		teachers
group by
		type

select
		case gender when 'm' then '男'
			when 'f' then '女'
end	 as gender_chinese,
count(name) as num
from
		students
group by
		gender

select
		s.student_id,
max(so.score),
min(so.score),
avg(so.score)
from
		students s
left join
		scores so
on
		s.student_id = so.student_id
where
		(s.student_id = '460093' or s.student_id = '735011')
and
		so.subject = '数学'
group by
		s.student_id

select
		s.student_id,
max(so.score),
min(so.score),
avg(so.score)
from
		students s
left join
		scores so
on
		s.student_id = so.student_id
where
		s.student_id = '460093' or s.student_id = '735011'
and
		so.subject = '数学'
group by
		s.student_id

select
		s.student_id,
max(so.score),
min(so.score),
avg(so.score)
from
		students s
left join
		scores so
on
		s.student_id = so.student_id
where
		s.student_id = '460093' or s.student_id = '735011'
group by
		s.student_id