mysql - 使用 join 和 group by 对同一个表进行 SQL 查询

Question

我对此查询有疑问:

SELECT source.cod,source.nome,source.ruolo,SUM(source.giocato),ROUND(SUM(source.fvoto)/SUM(source.giocato),2) as media,ROUND(SUM(source.voto)/SUM(source.giocato),2),SUM(source.gs),SUM(source.ass),SUM(source.amm),SUM(source.esp),SUM(source.rigsub),SUM(source.rigpar),source.stagione,parent.stagione,parent.cod,parent.squadra,parent.valore,parent.esiste,parent.gg
FROM db_dati source
WHERE source.ruolo=".$q." AND source.stagione=".$stagione."
GROUP BY source.cod,source.politico,source.stagione
HAVING source.politico=0
JOIN db_dati parent
ON source.stagione=parent.stagione AND source.cod=parent.cod
ORDER BY media DESC

但它总是回复我:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN db_dati parent ON source.stagione=parent.stagione AND source.cod=parent.cod' at line 1

Answer 1

您需要在 WHERE 子句之前连接表:

SELECT source.cod,source.nome,source.ruolo,SUM(source.giocato),ROUND(SUM(source.fvoto)/SUM(source.giocato),2) as media,ROUND(SUM(source.voto)/SUM(source.giocato),2),SUM(source.gs),SUM(source.ass),SUM(source.amm),SUM(source.esp),SUM(source.rigsub),SUM(source.rigpar),source.stagione,parent.stagione,parent.cod,parent.squadra,parent.valore,parent.esiste,parent.gg
FROM db_dati source
JOIN db_dati parent
ON source.stagione=parent.stagione AND source.cod=parent.cod
WHERE source.ruolo=".$q." AND source.stagione=".$stagione."
GROUP BY source.cod,source.politico,source.stagione
HAVING source.politico=0
ORDER BY media DESC