我的第一个查询返回一周的平均值：
SELECT POSITION, PLAYER, SUM( POINTS ) / COUNT( DISTINCT YEAR, WEEK ) AS 'avg'
FROM SCHEDULE WHERE WEEK = 1 GROUP BY PLAYER HAVING COUNT( DISTINCT YEAR, WEEK ) >2
ORDER BY avg DESC
我的第二个查询返回总体平均值：
SELECT POSITION, PLAYER, SUM( POINTS ) / COUNT( DISTINCT YEAR, WEEK ) AS 'avg'
FROM SCHEDULE GROUP BY PLAYER HAVING COUNT( DISTINCT YEAR, WEEK ) >2
ORDER BY avg DESC
我希望从查询2中减去查询1并以降序显示结果。
SELECT
WA.POSITION,
WA.PLAYER,
WA.avg as 'wk_avg',
OA.avg as 'overall_avg',
WA.avg - OA.avg as 'diff'
FROM
(SELECT POSITION, PLAYER, SUM( POINTS ) / COUNT( DISTINCT YEAR, WEEK ) AS 'avg'
FROM SCHEDULE WHERE WEEK = 1 GROUP BY PLAYER HAVING COUNT( DISTINCT YEAR, WEEK ) >2
) WA
INNER JOIN
(SELECT POSITION, PLAYER,SUM( POINTS ) / COUNT( DISTINCT YEAR, WEEK ) AS 'avg'
FROM SCHEDULE GROUP BY PLAYER HAVING COUNT( DISTINCT YEAR, WEEK ) >2
) OA
ON WA.POSITION = OA.POSITION and WA.PLAYER = OA.PLAYER
ORDER BY WA.avg - OA.avg DESC
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句