sql rank() 根据销售次数，生成排名列

people table schema

id
name
sales table schema

id
people_id
sale
price

select 
  p.id,
  p.name,
  count(p.id) as sale_count,
  Rank() over(order by count(p.id) desc) as sale_rank
from
  people p,
  sales s
where 
  p.id = s.people_id
group by p.id

结果如下：

id  name                sale_count  sale_rank
9   Bethel Wuckert          18          1
4   Joannie Haley           16          2
7   Jailyn Hartmann V       10          3
10  Dr. Adolph Halvorson    10          3
5   Arlie Mitchell          10          3
6   Myra Tillman            10          3
1   Clarissa Shields V      9           7
8   Bobby Purdy             7           8
3   Gerson Goyette          6           9
2   Lois Marvin             4           10