select * from user_login_log limit 5;

SELECT *
FROM numbers_for_fun
WHERE a = 0
AND b != 0
AND (c / b) >= 0
ORDER BY id ASC;

SELECT 
    exam_date,
    truncate(avg(CASE WHEN subject = '语文' THEN score ELSE NULL END),0) AS chinese_score,
    truncate(AVG(CASE WHEN subject = '数学' THEN score ELSE NULL END),0) AS math_score,
    truncate(AVG(CASE WHEN subject = '英语' THEN score ELSE NULL END),0) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;

SELECT 
    exam_date,
    avg(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    AVG(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    AVG(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;

SELECT 
    exam_date,
    MIN(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    MIN(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    MIN(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;

SELECT 
    exam_date,
    MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
FROM scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
GROUP BY exam_date
ORDER BY exam_date;

select student_id,sum(score)
from scores
where exam_date = '2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having sum(score) > 330
order by student_id

select student_id,sum(score),exam_date
from scores
where exam_date = '2024-06-30' and subject in ('语文','数学','英语')
group by student_id
having sum(score) > 330
order by student_id

select
	case 
	whenyear(enter_date) >= 2010 then '青年教师'
	whenyear(enter_date) < 2000 then '资深教师'
	else '中年教师'
end as '职称',
count(*) as '数量'
from teachers
group by 职称

select
	case 
	whenyear(enter_date) >= 2010 then '青年教师'
	whenyear(enter_date) < 2000 then '资深教师'
	else '中年教师'
end as teacher_type,
count(*) as '数量'
from teachers
group by teacher_type

select 
	case 
when gender = 'm' then'男'
when gender = 'f' then'女'
		else'other'
	end as gender_text,
count(*) as student_count
from students
group by gender

select 
		case 
when gender = 'm' then '男'
when gender = 'f' then '女'
else 'other'
end as '性别'
from students

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

SELECT *
FROM numbers_for_fun
WHERE a = 0 AND (c * b) < 0
ORDER BY id ASC;

select * from numbers_for_fun
where (a = '0' and c = '0') and (b < '0' or b > '0')
order by id

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

select * from numbers_for_fun
where a = '0' and c = '0'
order by id DESC

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

SELECT * 
FROM numbers_for_fun
WHERE a = 0 AND b != 0
ORDER BY id ASC;

select * from stock_info
where list_date between '2006-06-01' and '2006-09-01' and industry = '银行'