SQL练习-去除指定前N项之和后求平均值。【开贴】

wuxiang_123

要求：


使用MQ完成的效果：

1. 思路：
   
2. 第一部分：找出排名值比指定值大中的最小值和其对应的总分，即获取临界值。用【指定值-临界值排名+临界值对应总分在原始数据源出现的次数】即为改临界值的总分多加了的次数，将此*临界值总分则为多加了的总分之和。
   
3. 第二部分：找出排名值比指定值小于或等于的总分之和
   
4. 第三部分：找出数据源的总分之和，将总数-指定值获得平均值的分母。
   
5. 第四部分：第一部分计算出多加的总分之和和第二部分计算的总分之和相加，即为应减去的指定前N值总分之和，然后用【(第三部的总分之和-指定前N值总分之和)/(总数-指定前N值)】从而得出需求的平均值。
   
6. 
   
7. OLE DB:
   
8. SELECT 班级,SUM(总分)/(SUM(数)-F1) AS 平均值 FROM
   
9. (SELECT F1 AS 班级,F2 AS 总分,1 AS 数 FROM [数据源$A2:B]
   
10. UNION ALL
    
11. SELECT A1.班级,-A1.临界总分*(A1.F1-A1.临界排名+SUM(1)),0 FROM
    
12. (SELECT 班级,F1,MAX(总分) AS 临界总分,MIN(排名) AS 临界排名 FROM
    
13. (SELECT A.F1 AS 班级,A.F2 AS 总分,C.F1,SUM(1) AS 排名 FROM (SELECT DISTINCT * FROM [数据源$A2:B])A,[数据源$A2:B]B,[结果$B1:B1]C
    
14. WHERE A.F1=B.F1 AND A.F2<=B.F2
    
15. GROUP BY A.F1,A.F2,C.F1
    
16. HAVING SUM(1)>C.F1)
    
17. GROUP BY 班级,F1)A1,
    
18. [数据源$A2:B]A2
    
19. WHERE A1.班级=A2.F1 AND A1.临界总分=A2.F2
    
20. GROUP BY A1.班级,A1.临界总分,A1.临界排名,A1.F1
    
21. UNION ALL
    
22. SELECT A2.班级,-SUM(A1.F2),0 FROM [数据源$A2:B]A1,
    
23. (SELECT A.F1 AS 班级,A.F2 AS 总分 FROM (SELECT DISTINCT * FROM [数据源$A2:B])A,[数据源$A2:B]B,[结果$B1:B1]C
    
24. WHERE A.F1=B.F1 AND A.F2<=B.F2
    
25. GROUP BY A.F1,A.F2,C.F1
    
26. HAVING SUM(1)<=C.F1)A2
    
27. WHERE A1.F1=A2.班级 AND A1.F2=A2.总分
    
28. GROUP BY A2.班级)Q1,[结果$B1:B1]
    
29. GROUP BY 班级,F1
    
30. 
    
31. MQ:
    
32. SELECT 班级,SUM(总分)/(SUM(数)-?) AS 平均值 FROM
    
33. (SELECT 班级,总分,1 AS 数 FROM [数据源$]
    
34. UNION ALL
    
35. SELECT A1.班级,-A1.临界总分*(?-A1.临界排名+SUM(1)),0 FROM
    
36. (SELECT 班级,MAX(总分) AS 临界总分,MIN(排名) AS 临界排名 FROM
    
37. (SELECT A.班级,A.总分,SUM(1) AS 排名 FROM (SELECT DISTINCT * FROM [数据源$])A LEFT JOIN [数据源$]B
    
38. ON A.班级=B.班级 AND A.总分<=B.总分
    
39. GROUP BY A.班级,A.总分
    
40. HAVING SUM(1)>?)
    
41. GROUP BY 班级)A1,
    
42. [数据源$]A2
    
43. WHERE A1.班级=A2.班级 AND A1.临界总分=A2.总分
    
44. GROUP BY A1.班级,A1.临界总分,A1.临界排名
    
45. UNION ALL
    
46. SELECT A1.班级,-SUM(A1.总分),0 FROM [数据源$]A1,
    
47. (SELECT A.班级,A.总分 FROM (SELECT DISTINCT * FROM [数据源$])A LEFT JOIN [数据源$]B
    
48. ON A.班级=B.班级 AND A.总分<=B.总分
    
49. GROUP BY A.班级,A.总分
    
50. HAVING SUM(1)<=?)A2
    
51. WHERE A1.班级=A2.班级 AND A1.总分=A2.总分
    
52. GROUP BY A1.班级)
    
53. GROUP BY 班级

复制代码

去掉前N项最大值后求平均值.rar (55.85 KB, 下载次数: 47)

2012-4-21 09:10 上传

点击文件名下载附件

该贴已经同步到 wuxiang_123的微博

fankairong

Sub s()
Set conn = New ADODB.Connection
Dim rst As New ADODB.Recordset
conn.ConnectionString = "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0';data source='" & ThisWorkbook.FullName & "'"
conn.Open conn.ConnectionString
rst.Source = "select sum(总分)/count(总分) from (select top count(班级)-3 总分 from [数据源$] where 班级='A' order by 总分)"
rst.Open , conn.ConnectionString
[l2].CopyFromRecordset conn.Execute(rst.Source)
End Sub
不过有个错误，好像Top的排名包含同名次的，会把所有排名为5 的加进来

卢子

SQL的排名不怎么喜欢，总觉得不太方便，先上个辅助列的做法，回头得闲搞个完整的

利用参数查询，里面的语句稍作变动，复制下来
SELECT `数据源$`.班级, avg(`数据源$`.总分) as 平均分
FROM `C:\Users\今朝\Desktop\数据.xls`.`数据源$` `数据源$`
WHERE (`数据源$`.排名>?) group by `数据源$`.班级

wuxiang_123

卢子 发表于 2012-4-18 22:25
SQL的排名不怎么喜欢，总觉得不太方便，先上个辅助列的做法，回头得闲搞个完整的

利用参数查询，里面的语 ...

对记录进行排序,再添加辅助列的确简单,但符合要求.
不对记录排序,然后直接以序列号作辅助列也不复杂,但这些都不符合要求.
不过,通常录入的数据,每行记录都是不重复的,本帖的数据是为了增加难度,从实际来说,不可取,仅作为技术交流.

wuxiang_123

fankairong 发表于 2012-4-18 23:38
Sub s()
Set conn = New ADODB.Connection
Dim rst As New ADODB.Recordset

呵呵,我第一次也是想利用TOP,利用TOP不指定ORDER BY子句时,返回结果集靠N项记录,然后对这些记录进行求和,不过测试没有成功,尽管语句理论上是可行的(又或者是我弄错了).
但是,使用这种方法,即使获得通过,其速度也会很慢,所以,我的答案用其他方法来代替实现了.

hai593

这个299真是不好整.....

levinjiang

top或者是dcount都无法实现，唯有等答案了

冷月無聲

select sum(总分)/count(*) from 数据源 where 总分 not in (select top 3 总分 from 数据源 order by 总分 desc)

冷月無聲

用公式avg求平均。

卢子

开帖？答案呢？？