SELECT
s.class_code,
COUNT(distinct s.student_id) AS num_students_90_plus,
ROUND(AVG(sc.score), 2) AS avg_score_90_plus,
t.name AS physics_teacher
FROM
students s
JOIN
scores sc ON s.student_id = sc.student_id
JOIN
teachers t ON t.class_code LIKE CONCAT('%', s.class_code, '%')
WHERE
sc.subject = '物理'
AND sc.score >= 90 and sc.exam_date='2024-06-30'
AND t.subject = '物理'
GROUP BY
s.class_code, t.name
ORDER BY
avg_score_90_plus DESC;
with a as (
select s.student_id,s.name,score,
rank()over(partition by grade_code order by score desc) as ranking
from students as s
inner join scores as sc
on s.student_id=sc.student_id
where sc.subject='物理'
and grade_code='S1'
)
select student_id,name,score,ranking from a
where ranking <=10
order by ranking
with a as (
select s.student_id,s.name,sc.score,
row_number()over(partition by s.grade_code order by sc.score desc)as rnk
from students as s
inner join scores as sc
on s.student_id=sc.student_id
where s.grade_code= 'S1' and sc.subject='物理')
select student_id ,name ,score,rnk
from a
where rnk <=10
order by rnk,student_id
select a.student_id,a.name,b.score,row_number()over(order by score desc) as rnk
from
(select student_id ,name from students
where grade_code='S1') a
inner join
(select student_id,subject,score from scores
where subject='物理') b
on a.student_id=b.student_id
limit 10
select a.student_id,a.name,b.score,row_number()over(order by score desc) as rnk
from
(select student_id ,name from students
where grade_code='S1') a
inner join
(select student_id,subject,score from scores
where subject='物理') b
on a.student_id=b.student_id
select a.student_id,a.name,b.score
from
(select student_id ,name from students
where grade_code='S1') a
inner join
(select student_id,subject,score from scores
where subject='物理') b
on a.student_id=b.student_id
order by score desc
select s.name,s.class_code,s.grade_code,t.name as head_teacher_name
from students as s
inner join teachers as t
on s.class_code = t.head_teacher
order by student_id
select distinct a.name,a.class_code,a.grade_code,t.name
from
( selectdistinct s.student_id,name,class_code,grade_code,subject
from students as s
inner join scores as sc
on s.student_id =sc.student_id) as a
inner join
teachers as t
on a.class_code = t.head_teacher
select distinct a.name,a.class_code,a.grade_code,t.head_teacher
from
( selectdistinct s.student_id,name,class_code,grade_code,subject
from students as s
inner join scores as sc
on s.student_id =sc.student_id) as a
inner join
teachers as t
on a.class_code = t.head_teacher
select cust_uid,cust_uid_1
from (
select distinct b.cust_uid,
b.mch_nm as b_mch_nm,
a.cust_uid_1,
a.mch_nmas a_mch_nm
from
(select cust_uid as cust_uid_1,mch_nm from mt_trx_rcd1
where cust_uid <>'MT10000') as a
right join
(select distinct cust_uid,mch_nm from mt_trx_rcd1
where cust_uid = 'MT10000') as b
on a.mch_nm =b.mch_nm
order by 3) as c
group by 1,2
having count(1)=14
select distinct c.cust_uid,b.cust_uid_1
from
(selectcust_uid from mt_trx_rcd1 where cust_uid ='MT10000' ) as c
join(
select cust_uid_1 from (
select cust_uid as cust_uid_1,mch_nm from mt_trx_rcd1
where mch_nm in ('品众素心素食餐厅','一枚帅哥做的菜','庄家界(千灯店)','黄记烘培宫廷桃酥王')
and cust_uid <> 'MT10000'
group by 1,2
order by 1,2) as a
group by 1
having count(1)=4
order by 1) b
select distinct c.cust_uid,b.cust_uid_1 from
(select cust_uid from mt_trx_rcd1where cust_uid ='MT10000') c
join (
select cust_uid_1 from (
select distinct cust_uid as cust_uid_1,mch_nm from mt_trx_rcd1
where (mch_nm ='庄家界(千灯店)' or mch_nm='黄记烘培宫廷桃酥王' or mch_nm='品众素心素食餐厅')
and cust_uid <> 'MT10000'
order by cust_uid ) a
group by 1
having count(1)=3
order by 1
) b
select c.cust_uid,b.cust_uid_1 from
(select cust_uid from mt_trx_rcd1where cust_uid ='MT10000') c
join (
select cust_uid_1 from (
select distinct cust_uid as cust_uid_1,mch_nm from mt_trx_rcd1
where (mch_nm ='庄家界(千灯店)' or mch_nm='黄记烘培宫廷桃酥王' or mch_nm='品众素心素食餐厅')
and cust_uid <> 'MT10000'
order by cust_uid ) a
group by 1
having count(1)=3
order by 1
) b
select distinct b.cust_uid,c.cust_uid_1
from
(select cust_uid from mt_trx_rcd1
wherecust_uid = 'MT10000') b
join
(select cust_uid as cust_uid_1
from(
selectcust_uid,mch_nm from mt_trx_rcd1
where (mch_nm='庄家界(千灯店)'or mch_nm='黄记烘培宫廷桃酥王')
and cust_uid<>'MT10000'
group by 1,2
order by cust_uid
) a
group by 1
having count(cust_uid)>1) c
select a.cust_uid,b.cust_uid_1,a.mch_nm
from
(select distinct cust_uid,mch_nm from mt_trx_rcd1
where mch_nm ='兰州李晓明拉面馆' and cust_uid ='MT10000') as a
join
(select distinct cust_uid as cust_uid_1,mch_nm from mt_trx_rcd1
where mch_nm ='兰州李晓明拉面馆' and cust_uid !='MT10000'
order by 1) as b
on a.mch_nm=b.mch_nm