时间戳

首页 > 解决方案 > 为什么我在派生表中使用计数时会收到错误代码 1064？

mysql - 为什么我在派生表中使用计数时会收到错误代码 1064？

问题描述

我从派生表中的计数语句中得到错误代码 1064 。我想从游戏表中计算玩家出现在任何给定位置的次数（ergo：他玩了多少游戏）。我弄错了语法，似乎无法通过谷歌搜索我的解释方式，因此解释和解决方案将非常有帮助。提前致谢。

SELECT 
    players.playerName AS Namn,
    players.playerNickName AS Smeknamn,
    players.playerBirthDate AS Födelsedatum,
    players.playerBirthPlace AS Födelseort,
    players.playerDeathDate AS Dödsdatum,
    players.playerDeathPlace AS Dödsort,
    players.playerOriginClub AS Moderklubb,
    players.playerProfession AS Yrke,
    players.playerCaps AS Landskamper,
    players.playerAllsvenskan AS Allsvensk_för_Gefle,
    CASE
        WHEN
            players.playerDeathDate IS NOT NULL
        THEN
            TIMESTAMPDIFF(YEAR,
                players.playerBirthDate,
                players.playerDeathDate)
        ELSE TIMESTAMPDIFF(YEAR,
            players.playerBirthDate,
            NOW())
    END AS År,
    players.playerCitizenship1 AS Medborgarskap1,
    players.playerCitizenship2 AS Medborgarskap2,
    players.playerRelations AS Släktskap,
    players.playerSports AS Andra_sporter,
    players.playerAltName AS Alternativt_namn,
    players.playerDeathReason AS Dödsorsak,
    CASE
        WHEN players.playerCoachID IS NOT NULL THEN 'Ja'
        ELSE 'Nej'
    END AS Gefletränare,
    CASE
        WHEN playerChairmanID IS NOT NULL THEN 'Ja'
        ELSE 'Nej'
    END AS Gefleordförande,
    players.playerPreviousClub1 AS Till_GIF_från_1,
    players.playerPreviousClubCountry1 AS Till_GIF_från_1_land,
    players.playerNextClub1 AS Från_GIF_till_1,
    players.playerNextClubCountry1 AS Från_GIF_till_1_land,
    players.playerPreviousClub2 AS Till_GIF_från_2,
    players.playerPreviousClubCountry2 AS Till_GIF_från_2_land,
    players.playerNextClub2 AS Från_GIF_till_2,
    players.playerNextClubCountry2 AS Från_GIF_till_2_land,
    players.playerPreviousClub3 AS Till_GIF_från_3,
    players.playerPreviousClubCountry3 AS Till_GIF_från_3_land,
    players.playerNextClub3 AS Från_GIF_till_3,
    players.playerNextClubCountry3 AS Från_GIF_från_1_land,
    players.playerMisc AS Kuriosa,
    player, count(*) from (SELECT 
    games.gamePos1 AS player
FROM
    games 
UNION ALL SELECT 
    games.gamePos2 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos3 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos4 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos5 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos6 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos7 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos8 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos9 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos10 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos11 AS player
FROM
    games
    UNION ALL SELECT 
    games.gameSub1 AS player
FROM
    games
    UNION ALL SELECT 
    games.gameSub2 AS player
FROM
    games
    UNION ALL SELECT 
    games.gameSub3 AS player
FROM
    games) Matcher group by player,
players.playerNote AS Anteckning
FROM
    games
        INNER JOIN
    players ON players.playerName = games.gamePos1
        OR players.playerName = games.gamePos2
        OR players.playerName = games.gamePos3
        OR players.playerName = games.gamePos4
        OR players.playerName = games.gamePos5
        OR players.playerName = games.gamePos6
        OR players.playerName = games.gamePos7
        OR players.playerName = games.gamePos8
        OR players.playerName = games.gamePos9
        OR players.playerName = games.gamePos10
        OR players.playerName = games.gamePos11
        OR players.playerName = games.gameSub1
        OR players.playerName = games.gameSub2
        OR players.playerName = games.gameSub3
GROUP BY players.playerID
ORDER BY players.playerName

标签： mysqlsqlcommon-table-expression

解决方案

你的问题在于你的 count(*) 所有玩家的语法都是错误的。

尝试但没有数据和想要的结果我只修复了语法错误

    SELECT 
        players.playerName AS Namn,
        players.playerNickName AS Smeknamn,
        players.playerBirthDate AS Födelsedatum,
        players.playerBirthPlace AS Födelseort,
        players.playerDeathDate AS Dödsdatum,
        players.playerDeathPlace AS Dödsort,
        players.playerOriginClub AS Moderklubb,
        players.playerProfession AS Yrke,
        players.playerCaps AS Landskamper,
        players.playerAllsvenskan AS Allsvensk_för_Gefle,
        CASE
            WHEN
                players.playerDeathDate IS NOT NULL
            THEN
                TIMESTAMPDIFF(YEAR,
                    players.playerBirthDate,
                    players.playerDeathDate)
            ELSE TIMESTAMPDIFF(YEAR,
                players.playerBirthDate,
                NOW())
        END AS År,
        players.playerCitizenship1 AS Medborgarskap1,
        players.playerCitizenship2 AS Medborgarskap2,
        players.playerRelations AS Släktskap,
        players.playerSports AS Andra_sporter,
        players.playerAltName AS Alternativt_namn,
        players.playerDeathReason AS Dödsorsak,
        CASE
            WHEN players.playerCoachID IS NOT NULL THEN 'Ja'
            ELSE 'Nej'
        END AS Gefletränare,
        CASE
            WHEN playerChairmanID IS NOT NULL THEN 'Ja'
            ELSE 'Nej'
        END AS Gefleordförande,
        players.playerPreviousClub1 AS Till_GIF_från_1,
        players.playerPreviousClubCountry1 AS Till_GIF_från_1_land,
        players.playerNextClub1 AS Från_GIF_till_1,
        players.playerNextClubCountry1 AS Från_GIF_till_1_land,
        players.playerPreviousClub2 AS Till_GIF_från_2,
        players.playerPreviousClubCountry2 AS Till_GIF_från_2_land,
        players.playerNextClub2 AS Från_GIF_till_2,
        players.playerNextClubCountry2 AS Från_GIF_till_2_land,
        players.playerPreviousClub3 AS Till_GIF_från_3,
        players.playerPreviousClubCountry3 AS Till_GIF_från_3_land,
        players.playerNextClub3 AS Från_GIF_till_3,
        players.playerNextClubCountry3 AS Från_GIF_från_1_land,
        players.playerMisc AS Kuriosa,
        player, 
        (select count(*) from (SELECT 
                    games.gamePos1 AS player
                FROM
                    games 
            UNION ALL 
                SELECT 
                    games.gamePos2 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos3 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos4 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos5 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos6 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos7 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos8 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos9 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos10 AS player
                FROM
                    games
                UNION ALL 
            SELECT 
                    games.gamePos11 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gameSub1 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gameSub2 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                games.gameSub3 AS player
                FROM
                    games group by player) t1 ) Matcher 
        ,
players.playerNote AS Anteckning
FROM
    games
        INNER JOIN
    players ON players.playerName = games.gamePos1
        OR players.playerName = games.gamePos2
        OR players.playerName = games.gamePos3
        OR players.playerName = games.gamePos4
        OR players.playerName = games.gamePos5
        OR players.playerName = games.gamePos6
        OR players.playerName = games.gamePos7
        OR players.playerName = games.gamePos8
        OR players.playerName = games.gamePos9
        OR players.playerName = games.gamePos10
        OR players.playerName = games.gamePos11
        OR players.playerName = games.gameSub1
        OR players.playerName = games.gameSub2
        OR players.playerName = games.gameSub3
GROUP BY players.playerID
ORDER BY players.playerName

推荐阅读