mysql - 添加额外选择后的sql错误

Question

在以下方面我将不胜感激

SELECT distinct playerid as le_player, 
    (select sum(score) from playerresults where playerid = le_player) as wins, 
    (select handicap from players where playerid = le_player) as handicap, playername, 
    (select count(playerid)*3 from playerresults where playerid = le_player)as totalgames, 
    (select count(playerid)*3 from playerresults where playerid = le_player) - (select sum(score) from playerresults where playerid = le_player)as lost, 
    round((select sum(score) from playerresults where playerid = le_player) / (select count(playerid)*3 from playerresults where playerid = le_player) * 100,2) as percent, 
    teams.team_name 
FROM playerresults 
    INNER JOIN teams on (select players.team_id from players where players.id = playerid) = teams.id 
WHERE playerresults.season = 2012 AND playerresults.league = 4 
ORDER BY wins desc,totalgames asc

在我添加以下行之前一直有效:

(select handicap from players where playerid = le_player) as handicap

这现在会产生一个Subquery returns more than 1 row错误。

Handicap 是添加到我的玩家表中的一个新字段，所以我认为我可以将那部分 sql 添加到我的原始字段中。

有什么想法吗？

谢谢dg

Answer 1

您可能要考虑对表使用连接而不是所有相关子查询:

SELECT playerid as le_player, 
    sum(pr.score) as wins,
    p.handicap,
    pr.playername, 
    count(pr.playerid)*3 as totalgames, 
    count(pr.playerid)*3 - sum(pr.score) as lost, 
    round(sum(pr.score) / (count(pr.playerid)*3) * 100,2) as percent, 
    t.team_name 
from playerresults pr
left join players p
    on pr.playerid = p.id
left join teams t
    on p.team_id = teams.id 
where pr.season = 2012 
    AND pr.league = 4 
group by pr.playerid
ORDER BY wins desc, totalgames asc;

或者甚至是这样的:

SELECT pr.playerid as le_player, 
    Score as wins,
    p.handicap,
    pr.playername, 
    CountPlayerId*3 as totalgames, 
    CountPlayerId*3 - Score as lost, 
    round(Score / (CountPlayerId*3) * 100,2) as percent, 
    t.team_name 
from
(
    select playerid,
        sum(pr.score) Score,
        count(pr.playerid) CountPlayerId
    from playerresults
    WHERE season = 2012 
       AND league = 4 
    group by playerid
) pr
left join players p
    on pr.playerid = p.id
left join teams t
    on p.team_id = teams.id 
where pr.season = 2012 
    AND pr.league = 4 
ORDER BY wins desc, totalgames asc;