sql两表查询与统计 Member 会员表 ----表结构 mid scores （积分） msg会员信息---表结构 mid school

发布网友发布时间：2022-08-15 15:17

共4个回答

热心网友时间：2023-09-16 16:32

先联合两张表进行查询，再用group by 分类汇总，最后用order by 对函数列进行排列。
select sum(Member.scores) as zongfen,Msg.school from Member
inner join Msg on Member.mid=Msg.mid group by Msg.school
order by zongfen desc

热心网友时间：2023-09-16 16:32

select t1.score_sum ,t2.school
from (select mid ,sum(scores ) as score_sum from Member group by mid) t1 ,msg t2
where t1.mid=t2.mid order by score_sum desc

热心网友时间：2023-09-16 16:32

select m.school , sum(m,scores) from Member mb, msg m
group by m.school
order by sum(m.scores) desc追问计算属于school的mid总积分（scores）

追答select m.school , sum(m,scores) from Member mb, msg m
where m.mid = mb.mid
group by m.school
order by sum(m.scores) desc

热心网友时间：2023-09-16 16:33

表结构。。追问Member 会员表 ----表结构
mid scores （积分）
msg会员信息---表结构
mid school