我们的学区有 38 所小学。孩子们参加了测试。各学校的平均分很分散,但我想比较每所学校前 10 名学生的平均分。
要求:仅使用临时表。
我以一种工作量很大、很容易出错的方式完成了这件事,如下所示。
(sch_code = 例如,9043;
-- scabbrev = 例如,“卡特”;
-- totpct_stu = 例如,61.3)
DROP TEMPORARY TABLE IF EXISTS avg_top10 ;
CREATE TEMPORARY TABLE avg_top10
( sch_code VARCHAR(4),
schabbrev VARCHAR(75),
totpct_stu DECIMAL(5,1)
);
INSERT
INTO avg_top10
SELECT sch_code
, schabbrev
, totpct_stu
FROM test_table
WHERE sch_code IN ('5489')
ORDER
BY totpct_stu DESC
LIMIT 10;
-- I do that last query for EVERY school, so the total
-- length of the code is well in excess of 300 lines.
-- Then, finally...
SELECT schabbrev, ROUND( AVG( totpct_stu ), 1 ) AS top10
FROM avg_top10
GROUP
BY schabbrev
ORDER
BY top10 ;
-- OUTPUT:
-----------------------------------
schabbrev avg_top10
---------- ---------
Goulding 75.4
Garth 77.7
Sperhead 81.4
Oak_P 83.7
Spring 84.9
-- etc...
问题:所以这可行,但是没有更好的方法吗?
Thanks!
PS——看起来像家庭作业,但这是,嗯……真实的。
Using 这项技术.
select sch_code,
schabbrev,
ROUND( AVG( totpct_stu ), 1 ) AS top10
from (select sch_code,
schabbrev,
totpct_stu,
@num := if(@group = sch_code, @num + 1, 1) as row_number,
@group := sch_code as dummy
from test_table
order by sch_code, totpct_stu desc) as x
where row_number <= 10
GROUP BY sch_code,
schabbrev
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)