mysql - 在 SQL 查询上使用 COALSCE 和 3 个 JOINS

Question

SELECT teams.t_id, teams.t_name, teams.t_icon
                (COALESCE((SELECT SUM(matches.m_hteam_score) FROM matches WHERE matches.m_tid = teams.t_id), 0) + 
                    COALESCE((SELECT SUM(matches.m_ateam_score) FROM matches WHERE matches.m_tid2 = teams.t_id), 0)
                ) as totalscore           
            FROM teams
                LEFT JOIN divisions ON divisions.d_id = matches.m_did
                LEFT JOIN matches ON matches.m_did = divisions.d_id
                LEFT JOIN leagues ON leagues.l_id = divisions.d_lid
            WHERE leagues.l_id = ? AND matches.m_finished = ? AND matches.m_did = ?

我的查询给我错误“teams.t_icon”不存在，即使 team 应该类似于表名称并且 t_icon 是表中的列。

我不明白它是如何不起作用的，但是我在这里的 COALESCE 似乎会影响它，使查询无法正确运行。我尝试了不同的方法，当我更改连接和表名称时，它根本无法正常工作。

有什么想法吗？

错误:语法错误或访问冲突:1305 FUNCTION groups.t_icon 不存在'

Answer 1

正如我看到的错误，您忘记了列名称后的 , 。试试这个

SELECT teams.t_id, teams.t_name, teams.t_icon,
                (COALESCE((SELECT SUM(matches.m_hteam_score) FROM matches WHERE matches.m_tid = teams.t_id), 0) + 
                    COALESCE((SELECT SUM(matches.m_ateam_score) FROM matches WHERE matches.m_tid2 = teams.t_id), 0)
                ) as totalscore           
            FROM teams, matches
                LEFT JOIN divisions ON divisions.d_id = matches.m_did
                AND matches.m_did = divisions.d_id
                LEFT JOIN leagues ON leagues.l_id = divisions.d_lid
            WHERE leagues.l_id = ? AND matches.m_finished = ? AND matches.m_did = ?

在您的查询中，球队与比赛、分区和联赛之间没有任何关系