上次问了个分组最大问题，这次问个分组前5问题。

[不假思索]

分组排序取前 n，再合成。

例如，table存储各个人历年年度总分明细，希望最终取得历年、每年前5名单:
年度 总分 姓名
2005 xx a1
2005 xx a2
2005 xx a3
2005 xx a4
2005 xx a5
2006 xx b1
2006 xx b2
2006 xx b3
2006 xx b4
2006 xx b5
2007 xx c1
2007 xx c2
2007 xx c3
2007 xx c4
2007 xx c5

如果：select top 5 年度,总分,姓名 from table order by 总分 descending，这样的话是合成排序，效果是：

年度 总分 姓名
2005 xx a1
2007 xx c2
2006 xx b1
2005 xx a4
2007 xx c1

历年合成、前5。
而不是每年前5，再合成。

VFP 9.0，一条SQL能不能做到？

[wwwwb]

select * from tt a where 5<(select count(*) from tt where a.年度=年度
and a.总分>=总分)

[不假思索]

没看明白。

[ACMAIN_CHM]

参考下贴中的多种方法

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....

[不假思索]

多谢两位! 受你们的启发搞掂!
这样写可能更容易理解一点:

select count( * ), table.年度, table.总分,table.姓名 from table, table a ;
WHERE table.年度 = a.年度 AND table.总分 <= a.总分;
group by table.年度,table.总分,table.姓名 ;
HAVING COUNT( * ) <= 5;
ORDER BY table.年度,table.总分

[不假思索]

我把这个问题总结归纳了一下。

统计上经常会遇到分组排序问题，凭直觉一句SQL应该可以搞掂，但一直没有想出办法来。以前变通一下混过去算了。最近又遇到这个问题，心里很不舒服，下决心一劳永逸、彻底解决。

于是上网搜索，发帖求教。

很快CSDN的wwwwb大侠手拔刀相助：（ ACMAIN_CHM也提供了另一个参考链接，再次一并谢过两位！）


select * from tt a where 5<(select count(*) from tt where a.年度=年度 and a.总分>=总分)

刚看到这句SQL的时候愣了一下。
5< ？和平常习惯大不同。
=年度、>=总分 ？这个“年度”、“总分”怎么取值？难道还要手工录入？



慢慢回味之下，渐渐觉得这个算法可能涉及笛卡尔乘积、分组。
将信将疑之中把这句SQL执行了一遍，结果没有提示语法错误，不用手工输入数据，输出数据看上去也比较接近预期。

于是开始分步、深入尝试。假设目的是获得每年总分最高前5名单。过程如下。

首先构建测试数据。随手在excel里做了个表，每个人在同一年度只出现一次。之所以用excel表，是因为调整测试数据容易，可以自动产生序列。
年度 姓名 总分
2005 x1 110
2005 x2 120
2005 x3 130
2005 x4 140
2005 x5 150
2005 x6 160
.....
2005 x12 220
2005 x13 230
2005 x14 240
2005 x15 250
2005 y1 230
2005 y2 240
2005 y3 250
2006 x13 115
2006 x14 125
2006 x15 135
....
2007 x3 172
2007 x4 182
2007 x5 192
2007 x6 202


然后用odbc连接到VFP 9.0，建立远程视图。


产生笛卡尔积。
Select rv.*,ra.* From rv_group As rv, rv_group As ra

一切顺利。
隐隐约约觉察到where的用处，于是：



首先剔除不同年度的匹配
Select rv.*,ra.* From rv_group As rv, rv_group As ra;
Where rv.年度 = ra.年度;
Order By rv.年度,rv.总分 descending


数据大减。


Where条件再加上 And rv.总分 <= ra.总分
Select rv.*,ra.* From rv_group As rv, rv_group As ra;
Where rv.年度 = ra.年度 And rv.总分 <= ra.总分;
Order By rv.年度,rv.总分 Descending, rv.姓名,ra.总分 Descending, ra.姓名

ra总分比rv小的匹配被剔除。实际上这获得ra的一个不低于rv当前总分的清单。

不低于rv当前总分的人越少，就是超过rv当前总分的越少，说明rv当前总分高、排名靠前。等到不低于rv当前总分只剩下自己的时候，排名就是第一了！
以按总分不低于自己的人数为依据排序，不就是一张总分排名表？！
这下豁然开朗，问题解决！


于是再尝试SQL：
Select COUNT( ra.姓名 ) as 排名,rv.* From rv_group As rv, rv_group As ra;
where rv.年度 = ra.年度 And rv.总分 <= ra.总分;
Group By rv.年度, rv.总分,rv.姓名;
Order By rv.年度,rv.总分 Descending, rv.姓名

结果如下：


排名 年度 姓名 总分
2 2005 x15 250
2 2005 y3 250
4 2005 x14 240
4 2005 y2 240
6 2005 x13 230
6 2005 y1 230
7 2005 x12 220
...
1 2006 y6 195
2 2006 y5 185
3 2006 y4 175
4 2006 y3 165
5 2006 y2 155
...
1 2007 x6 202
2 2007 x5 192
3 2007 x4 182
4 2007 x3 172
5 2007 x2 162
....


如果取前5，则添加 Having......，计算组内行数。
Select COUNT( ra.姓名 ) as 排名,rv.* From rv_group As rv, rv_group As ra;
Where rv.年度 = ra.年度 And rv.总分 <= ra.总分;
Group By rv.年度, rv.总分,rv.姓名;
Having COUNT( * ) <= 5;
Order By rv.年度,rv.总分 Descending, rv.姓名

结果如下：

排名 年度 姓名 总分
2 2005 x15 250
2 2005 y3 250
4 2005 x14 240
4 2005 y2 240
1 2006 y6 195
2 2006 y5 185
3 2006 y4 175
4 2006 y3 165
5 2006 y2 155
1 2007 x6 202
2 2007 x5 192
3 2007 x4 182
4 2007 x3 172
5 2007 x2 162


大功告成！


同分并列问题


上述算法按不超过前5设计。如果出现同分并列，例如总分前4都只有1人，但第5有2人，那么总分前5就有6人了，这种情况下计算结果只输出前4。
其它还有诸如并列时排名靠前、还是靠后之类，不同情况有不同需要，大家注意调整算法。

可能wwwwb大侠没留意，他的算法实际上是去掉最低5个总分。把两个where不等号稍微调整了一下，改成

select * from tt a where 5 >= (select count(*) from tt where a.年度 = 年度 and a.总分 <= 总分)

就能达到目的了。


wwwwb的算法和我还是有所不同，把他的SQL再改一下，思路就容易理解了。
Select rv.* From rv_group As rv ;
Where 5 >= ( Select count( * ) From rv_group As ra;
Where rv.年度 = ra.年度 and rv.总分 <= ra.总分 )

通过子查询计算总分高于主查询当前总分的行数，
当该行数不超过5时，主查询当前行符合预期要求。

这个算法更精炼。


另外，我测试了一下，5 >= 不能放在子查询后面，写成 ( Select.........) <= 5，提示语法错误。


（ 以上测试，基于Windows XP + VFP 9.0 ）

[wwwwb]

呵呵，总结得不错