统计上经常会遇到分组排序问题,凭直觉一句SQL应该可以搞掂,但一直没有想出办法来。以前变通一下混过去算了。最近又遇到这个问题,心里很不舒服,下决心一劳永逸、彻底解决。
于是上网搜索,发帖求教。
很快CSDN的wwwwb大侠手拔刀相助:( ACMAIN_CHM也提供了另一个参考链接,再次一并谢过两位!)
select * from tt a where 5<(select count(*) from tt where a.年度=年度 and a.总分>=总分)
刚看到这句SQL的时候愣了一下。
5< ?和平常习惯大不同。
=年度、>=总分 ?这个“年度”、“总分”怎么取值?难道还要手工录入?
慢慢回味之下,渐渐觉得这个算法可能涉及笛卡尔乘积、分组。
将信将疑之中把这句SQL执行了一遍,结果没有提示语法错误,不用手工输入数据,输出数据看上去也比较接近预期。
于是开始分步、深入尝试。假设目的是获得每年总分最高前5名单。过程如下。
年度 姓名 总分
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
数据大减。
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当前总分只剩下自己的时候,排名就是第一了!这下豁然开朗,问题解决!
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
....
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。
其它诸如并列时排名按前、还是后取值之类也会有这种尴尬,不同工作环境下会有不同要求,大家要注意调整算法。
select * from tt a where 5 >= (select count(*) from tt where a.年度 = 年度 and a.总分 <= 总分)
就能达到目的了。
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 )
联系客服