VFP9 分组排序

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

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

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

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

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

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

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

1. 首先构建测试数据。随手在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，建立远程视图。

2.  产生笛卡尔积。

   Select rv.*,ra.* From rv_group As rv, rv_group As ra

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

3. 首先剔除不同年度的匹配

   Select rv.*,ra.* From rv_group As rv, rv_group As ra;
   Where rv.年度 = ra.年度;
   Order By rv.年度,rv.总分 descending
   

   数据大减。

4. 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当前总分只剩下自己的时候，排名就是第一了！
   以按总分不低于自己的人数为依据排序，不就是一张总分排名表？！

   这下豁然开朗，问题解决！

5. 于是再尝试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
   ....
   

6. 如果取前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
   

   大功告成！

7. 同分并列问题
   

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

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

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

   就能达到目的了。

9. wwwwb的算法和我还是有所不同，把他的SQL再改一下，思路就容易理解了。

   Select   rv.*  From  rv_group  As rv  ;
   Where  5 >= ( Select  count( * ) From rv_group As ra;
                            Where rv.年度 = ra.年度 and rv.总分 <= ra.总分 )
   

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

   这个算法更精炼。
   

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

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