select distinct
gd_id,
gd_nm,
gd_typ
from gd_inf gd
join xhs_pchs_rcd pchs
on gd.gd_id=pchs.mch_id
left join xhs_fav_rcd fav
on pchs.mch_id=fav.mch_id
where fav.mch_id is null;
select distinct
gd.gd_id,
gd.gd_nm,
gd.gd_typ
from
gd_inf gd
join
xhs_fav_rcd fav
on gd.gd_id=fav.mch_id
left join
xhs_pchs_rcd pchs
on fav.mch_id=pchs.mch_id
where pchs.mch_id is null;
select
gd.gd_typ,
count(distinct pchs.cust_uid) as pchs_count
from xhs_pchs_rcd pchs
join
gd_inf gd
on pchs.mch_id=gd.gd_id
group by
gd.gd_typ
order by
pchs_count desc
limit 1;
select
gd.gd_typ,
count(pchs.mch_id) as pchs_count
from xhs_pchs_rcd pchs
join
gd_inf gd
on pchs.mch_id=gd.gd_id
group by
gd.gd_typ
order by
pchs_count desc
limit 1;
select
gd.gd_id,
gd.gd_nm,
count(fav.fav_trq) as fav_count
from xhs_fav_rcd fav
left join
gd_inf gd
on
fav.mch_id=gd.gd_id
group by
gd.gd_id,
gd.gd_nm
order by
fav_count desc
limit 1;
select
gd.gd_typ,
count(distinct(pchs.cust_uid)) as pchs_count
from
xhs_pchs_rcd pchs
join
gd_inf gd on pchs.mch_id=gd.gd_id
group by
gd.gd_typ
order by
pchs_count desc
limit 1;
select
gd.gd_typ,
count(distinct(pchs.pch_trq)) as pchs_count
from
xhs_pchs_rcd pchs
join
gd_inf gd on pchs.mch_id=gd.gd_id
group by
gd.gd_typ
order by
pchs_count desc
limit 1;
select
gd.gd_id,
gd.gd_nm,
count(distinct(pchs.pch_trq)) as pchs_count
from
xhs_pchs_rcd pchs
join
gd_inf gd on pchs.mch_id=gd.gd_id
group by
gd.gd_id,
gd.gd_nm
order by
pchs_count desc
limit 1;
select
gd.gd_id,
gd.gd_nm,
count(fav.fav_trq) as fav_count
from
xhs_fav_rcd fav
join
gd_inf gd on gd.gd_id=fav.mch_id
group by
gd.gd_id,
gd.gd_nm
order by
fav_count desc
limit 1;
select
gd.gd_id,
gd.gd_nm,
count(fav.fav_trq) as fav_count
from
xhs_fav_rcd fav
join
gd_inf gd on gd.gd_id=fav.mch_id
group by
gd.gd_id,
gd.gd_nm
order by
fav_count desc
limit 5;
select
gd.gd_typ,
count(distinct(cust_uid)) as pch_count
from
xhs_pchs_rcd pch
join
gd_inf gd on gd.gd_id=pch.mch_id
group by
gd.gd_typ
order by
pch_count desc
limit 1;
select
gd.gd_id,
gd.gd_nm,
count(fav.fav_trq) as fav_count
from
xhs_fav_rcd fav
join
gd_inf gd on fav.mch_id=gd.gd_id
group by
gd.gd_id,
gd.gd_nm
order by
fav_count desc
limit 1;
select
gd.gd_id,
gd.gd_nm,
count(fav.fav_trq) as fav_count
from
xhs_fav_rcd fav
join
gd_inf gd on fav.mch_id=gd.gd_id
group by
gd.gd_id,gd.gd_nm
order by
fav_count desc
limit 1;
select
gd.gd_typ,
count(pchs.pch_trq) as pchs_count
from
xhs_pchs_rcd pchs
join
gd_inf gd on pchs.mch_id=gd.gd_id
group by
gd.gd_typ
order by
gd.gd_typ desc
limit 1;
select
gd.gd_typ,
count(pchs.pch_trq) as pchs_count
from
xhs_pchs_rcd pchs
join
gd_inf gd on pchs.mch_id=gd.gd_id
group by
gd.gd_typ
order by
gd.gd_typ
limit 1;
select
case when sc.score>=110 then '[110,120]'
when sc.score>=90 then'[90,110)'
when sc.score>=60 then'[60,90)'
else '(60,0]'
end as score_range,
count(*) as num_students
from students st
join scores sc
on st.student_id=sc.student_id
where sc.exam_date='2024-06-30' and sc.subject='数学'
group by score_range
ORDER BY
score_range DESC;
select
case when sc.score>=110 then '[110,120]'
when sc.score>=90 then'[90,110)'
when sc.score>=60 then'[60,90)'
else '(60,0]'
end as score_range,
count(*) as num_students
from students st
join scores sc
on st.student_id=sc.student_id
where sc.exam_date='2024-06-30' and sc.subject='数学'
group by score_range;
select
case when sc.score>=110 then '[110,120]'
when sc.score>=90 then'[90,110)'
when sc.score>=60 then'[60,90)'
else '(60,0]'
end as score_range,
count(*) as num_students
from students st
join scores sc
on st.student_id=sc.student_id
where sc.exam_date='2024-06-30' and sc.subject='数学'
group by score_range
order by num_students;
select
case when sc.score>=110 then '[110,120]'
when sc.score>=90 then'[90,110)'
when sc.score>=60 then'[60,90)'
else '(60,0]'
end as score_range,
count(*) as num_students
from students st
join scores sc
on st.student_id=sc.student_id
where exam_date='2024-06-30' and sc.subject='数学'
group by score_range
order by num_students;
select
case when sc.score>=110 then '[110,120]'
when sc.score>=90 then '[90,110)'
when sc.score>=60 then '[60,90)'
else '[0,60)'
end as score_range,
count(st.student_id) as num_students
from students st
join scores sc
on st.student_id=sc.student_id
where sc.subject='数学' and sc.exam_date='2024-06-30'
group by score_range;