同时求平均分和名次

[o0风箫吟0o]

各位朋友：

现在有一个表：ChengJiBiao ,其内容如下：

班级ID 语文
1 80
1 90
1 70
1 60
1 80
2 90
2 70
2 70
2 60
2 90
2 80

能不能通过一条Select语句得到如下结果：

班级ID 语文平均分 平均分名次
1 76.00 2
2 76.67 1

也就是计算出各班的 平均分 和 班级的平均分名次

请各位帮帮我。谢谢大家。

[sinat_26889667]

select 班级ID，avg（语文） 语文 from ChengJiBiao group by 班级ID order by 语文

[回马枪]

Create Cursor ChengJiBiao (班级ID I, 语文 N(6, 2))
Insert Into ChengJiBiao Values (1, 80)
Insert Into ChengJiBiao Values (1, 90)
Insert Into ChengJiBiao Values (1, 70)
Insert Into ChengJiBiao Values (1, 60)
Insert Into ChengJiBiao Values (1, 80)
Insert Into ChengJiBiao Values (2, 90)
Insert Into ChengJiBiao Values (2, 70)
Insert Into ChengJiBiao Values (2, 70)
Insert Into ChengJiBiao Values (2, 60)
Insert Into ChengJiBiao Values (2, 90)
Insert Into ChengJiBiao Values (2, 80)

Select 班级ID, 语文 As 语文平均分, (Select 1 + Count(语文);
    From (Select 班级ID, Avg(语文) As 语文 ;
    From ChengJiBiao ;
    Group By 班级ID) As t1 ;
    Where 语文 > T2.语文) As 平均分名次 ;
    From (Select 班级ID, Avg(语文) As 语文 ;
    From ChengJiBiao ;
    Group By 班级ID) As t2 ;
    Order By 班级ID  && Desc 

[o0风箫吟0o]

谢谢zyg8108大侠，完美解决我的问题。谢谢

[o0风箫吟0o]

帖子先留一留，听听其他高手的意见。

[回马枪]

我也想听听其他高手的意见 比如 1. 技巧问题： 如何用一条 SQL 语句，字符必须限制在 100字符之间 2. 速度问题 哪条语句的运算速度最快 比如有10W条记录 可以秒杀吗 3. 如何模拟 SQL 2K5 的四个排名函数：Row_Number()、Rank()、Dense_Rank() 和 Ntile() 4. 另类算法 不考虑优化问题 希望有一种创造性的、从没见过的、另类的排名算法

[都市夜猫]

大概 vfp 中这样也可以

select 班级id, 平均分, recno() as 名次 ;
from ( ;
    select 班级id, Avg(语文) as 平均分 ;
    from ChengJiBiao group by 班级id order by 平均分 desc ;
) t

[o0风箫吟0o]

引用 6 楼 dkfdtf 的回复:

大概 vfp 中这样也可以

select 班级id, 平均分, recno() as 名次 ;
from ( ;
    select 班级id, Avg(语文) as 平均分 ;
    from ChengJiBiao group by 班级id order by 平均分 desc ;
) t

用 recno() 来填充名次的方式，最大的问题是不能实现“同分同名”，不知道说得对不对。

[回马枪]

*!*	模拟 SQL 2005 Rank() - 不联号排名，和 Dense_Rank() - 联号排名函数
*!*	解决同分排名问题

Create Cursor ChengJiBiao (学生ID I, 分数 I)
Insert Into ChengJiBiao Values ( 2, 76)
Insert Into ChengJiBiao Values ( 4, 80)
Insert Into ChengJiBiao Values ( 6, 90)
Insert Into ChengJiBiao Values ( 8, 70)
Insert Into ChengJiBiao Values (10, 60)
Insert Into ChengJiBiao Values (12, 80)
Insert Into ChengJiBiao Values (11, 99)
Insert Into ChengJiBiao Values ( 9, 72)
Insert Into ChengJiBiao Values ( 7, 70)
Insert Into ChengJiBiao Values ( 5, 60)
Insert Into ChengJiBiao Values ( 3, 90)
Insert Into ChengJiBiao Values ( 1, 81)


Select T1.学生ID, T1.分数, Count(T2.分数) + 1 As Rank, ;
    Count(Distinct T2.分数) + 1 As Dense_Rank From ChengJiBiao T1 ;
    LEFT Join ChengJiBiao T2 On T1.分数 > T2.分数 ;
    Group By T1.学生ID, T1.分数 Order By T1.分数, T1.学生ID

[回马枪]

身边没有 SQL 2K5 做比对 应该不会有问题吧

[回马枪]

*!*	正排名
Select T1.学生ID, T1.分数, Count(T2.分数) + 1 As Rank, ;
    Count(Distinct T2.分数) + 1 As Dense_Rank From ChengJiBiao T1 ;
    LEFT Join ChengJiBiao T2 On T2.分数 > T1.分数 ;
    Group By T1.学生ID, T1.分数 Order By T1.分数 Desc, T1.学生ID

[lygcw9602]

贴上足够量的测试数据

[lygcw9602]

Create Cursor ChengJiBiao (班级 I, 分数 I)
Insert Into ChengJiBiao Values ( 1, 76)
Insert Into ChengJiBiao Values ( 1, 80)
Insert Into ChengJiBiao Values ( 1, 85)
Insert Into ChengJiBiao Values ( 2, 70)
Insert Into ChengJiBiao Values ( 2, 60)
Insert Into ChengJiBiao Values ( 2, 80)
Insert Into ChengJiBiao Values ( 3, 99)
Insert Into ChengJiBiao Values ( 3, 72)
Insert Into ChengJiBiao Values ( 3, 70)
Insert Into ChengJiBiao Values ( 4, 60)
Insert Into ChengJiBiao Values ( 4, 90)
Insert Into ChengJiBiao Values ( 4, 81)


SELECT 班级,AVG(分数) 平均分,SUM(分数) 总分数,000 名次 FROM ChengJiBiao GROUP BY 1 ORDER BY 2 DESC INTO CURSOR Temp READWRITE 

* 方法一
Mc=1
Pjf=平均分
SCAN 
    REPLACE 名次 WITH IIF(平均分=Pjf,Mc,RECNO())
    Mc=名次
    Pjf=平均分
ENDSCAN
BROWSE 

* 方法二
UPDATE Temp SET Temp.名次=(SELECT COUNT(*)+1 FROM Temp Bb WHERE Temp.平均分<Bb.平均分) FROM Temp
SELECT Temp
BROWSE 

* 如果记录数超过5000条，建议使用方法一。

[o0风箫吟0o]

谢谢大家的帮助，我要好好消化一下。

[o0风箫吟0o]

用Select 和 SCAN REPLACE ENDSCAN 方式，理论上说哪一种更快一些，数据如果是2万条呢？

[o0风箫吟0o]

lygcw9602 老师的思路很巧妙，只扫描一次表就完成计算。 zyg8108 老师的思路很简洁： Select T1.学生ID, T1.分数, Count(T2.分数) + 1 As Rank, ; Count(Distinct T2.分数) + 1 As Dense_Rank From ChengJiBiao T1 ; LEFT Join ChengJiBiao T2 On T2.分数 > T1.分数 ; Group By T1.学生ID, T1.分数 Order By T1.分数 Desc, T1.学生ID 是不是Count(T2.分数)统计“记录数”这么多次呢？ 请两位老师另见怪，只是想找到最快的方法。