我正试图写一个SELECT语句,对玩家的原始分数取平均值,从200的基础上减去原始分数,然后将其乘以90%来计算玩家的差点。
这个代码似乎正确地应用了计算,但为每个玩家创建了许多不同的平均值。
SELECT BOWLERID "Bowler ID", b.BOWLERFIRSTNAME || ' ' ||
b.BOWLERLASTNAME "Bowler Name",
AVG(bs.HANDICAPSCORE)+ -200 * 0.90 "Handicap"
FROM BOWLER_SCORES bs
JOIN BOWLERS b USING (BOWLERID)
GROUP BY BOWLERID, b.BOWLERFIRSTNAME, b.BOWLERLASTNAME,
bs.HANDICAPSCORE
ORDER BY bs.HANDICAPSCORE DESC;
我认为这是我的表连接设置的问题,但我不能完全确定。
我是SQL的新手,所以任何建议和建议都非常感谢。
编辑:
因此,在删除bs.HANDICAPSCORE并将ORDERBY更改为"盘口"时,我发现平均计算是不正确的。我还需要四舍五入到没有小数点。
更新代码:
SELECT BOWLERID "Bowler ID", b.BOWLERFIRSTNAME || ' ' ||
b.BOWLERLASTNAME "Bowler Name",
AVG(bs.HANDICAPSCORE)+ -200 * 0.90 "Handicap"
FROM BOWLER_SCORES bs
JOIN BOWLERS b USING (BOWLERID)
GROUP BY BOWLERID, b.BOWLERFIRSTNAME, b.BOWLERLASTNAME
ORDER BY "Handicap" DESC;
我的AVG语句设置正确吗?
看起来你的计算应该是(200-平均值(*90%,所以
SELECT BOWLERID "Bowler ID", b.BOWLERFIRSTNAME || ' ' || b.BOWLERLASTNAME "Bowler Name", (200 - AVG(bs.HANDICAPSCORE)) * 0.90 "Handicap"
FROM BOWLER_SCORES bs
JOIN BOWLERS b USING (BOWLERID)
GROUP BY BOWLERID, b.BOWLERFIRSTNAME, b.BOWLERLASTNAME
ORDER BY "Handicap" DESC;
您有AVG()
函数,但也包括GROUP BY
中的字段。此外,我认为由于操作员优先级的原因,您的计算是错误的:
SELECT BOWLERID as "Bowler ID", b.BOWLERFIRSTNAME || ' ' || b.BOWLERLASTNAME as "Bowler Name",
(200 - AVG(bs.HANDICAPSCORE)) * 0.90 as Handicap
FROM BOWLER_SCORES bs JOIN
BOWLERS b
USING (BOWLERID)
GROUP BY BOWLERID, b.BOWLERFIRSTNAME, b.BOWLERLASTNAME
ORDER BY AVG(bs.HANDICAPSCORE) DESC;