ID, NAME, SCORE
1, zhao, 90
2, qian, 80
3, zhao, 60
4, zhao, 90
5, qian, 80
6, sun, 100
7, sun, 100
8, li, 70
每个人的成绩平均分(自己的），然后按最高分排序。并且输出这个人出现的次数。
输出如下：
NAME, AVG, TIMES
SUN, 100, 2
ZHAO, 80, 3
QIAN, 80, 2
LI, 70, 1
------解决思路----------------------
with data(ID, NAME, SCORE) as
(
select 1, 'zhao', 90 union all
select 2, 'qian', 80 union all
select 3, 'zhao', 60 union all
select 4, 'zhao', 90 union all
select 5, 'qian', 80 union all
select 6, 'sun', 100 union all
select 7, 'sun', 100 union all
select 8, 'li', 70
)
select
data.name
,AVG(data.score) as avgscore
,count(1) as times
from data
group by name
order by avgscore desc