统计上经常会遇到分组排序问题,凭直觉一句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 )