php - 计算前后测试的改进百分比(MYSQL 和 PHP)

Question

这是 SQLFiddle 中此设置的链接 - http://sqlfiddle.com/#!2/b652d/1

我想找到所有同时进行了前测和后测的学生。然后我想获得从预测试到后测试的总体百分比变化。

这是我的查询，它没有为后期测试返回任何结果:

 SELECT studid, TestDate, pre, post, ROUND( (
( post - pre ) / pre ) *100, 1 ) AS percentChange
FROM bodpod
WHERE ID2 =  'fall'
AND studid !=  ''
GROUP BY studid
HAVING COUNT( DISTINCT ID1 ) >1
AND TestDate LIKE  '%2013%'

这是一名学生的示例数据:

我如何重写该查询以获得期望的结果，即在 2013 年秋季参加前后测试的所有学生的总体百分比变化？

Answer 1

编辑#2:

在查看了您的 fiddle 中的数据并猜测原始数据已经是百分比之后，我建议如下:

select round(sum(After)/count(After) - sum(B4)/count(B4), 2) '% Change' from
(select studid, sum(pre) 'B4' , sum(post) 'After' 
from bodpod
group by studid
having After > 0 and B4 > 0) data;

更正:

SELECT t1.studid, t1.TestDate, t1.pre, t2.post, ROUND( (
( t2.post - t1.pre ) / t1.pre ) *100, 1 ) AS percentChange
FROM bodpod t1
join bodpod t2 on t1.studid = t2.studid
WHERE t1.ID2 =  'fall'
AND t1.studid !=  ''
and t2.pre=0
GROUP BY studid
HAVING COUNT( DISTINCT t1.ID1 ) >1
AND TestDate LIKE  '%2013%'

当我运行它时(在 MySQL Workbench 中)我得到:

studid  TestDate    pre post    percentChange
6093191 8/12/2013 10:38 27.7    22.8    -17.7

对于累积百分比(假设不需要加权):

SELECT  ROUND( ( (sum(After) - sum(B4)) / sum(B4) ) *100, 1 ) AS percentChange from
(SELECT t1.studid, t1.TestDate, t1.pre as B4, t2.post as After
FROM bodpod t1
join bodpod t2 on t1.studid = t2.studid
WHERE t1.ID2 =  'fall'
AND t1.studid !=  ''
and t2.pre=0
GROUP BY studid
HAVING COUNT( DISTINCT t1.ID1 ) >1
AND TestDate LIKE  '%2013%') scores