这是一个我遇到的需求啊,要做一个简单的报表。就是找出每一个城市的分数最高的前10。

首先我第一想到的就是分组,分组之后取前几嘛。但是这有个问题,要说的sql的单值原则。group之后的,对于分组以外的列都必须是明确唯一的,比如你可以group之后,取每组的最大或最小,但是你不能取每组中的一个list。

首先这个group这个解决方案从一开始就是错的,就导致陷入死胡同。最后解决是通过开窗函数解决,记录一下。

方案一(推荐)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT city,score,rank
FROM
(
SELECT *,
IF(@p=city,
CASE
WHEN @s=score THEN @r
WHEN @s:=score THEN @r:=@r+1
END,
@r:=1 ) AS rank,
@p:=city,
@s:=score
FROM cs,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
ORDER BY city,score DESC
)s
WHERE rank <10;

方案二

1
2
3
4
5
SELECT * FROM cs c
WHERE (
SELECT count(*) FROM cs
WHERE city=c.city AND score>c.score )<10
ORDER BY city,score DESC