mysql - 编写 SQL 查询所需的帮助

Question

我在 Sopocosy 体育提要文档中找到了一个查询。我已更改此查询以满足我的需要。这是我的查询

SELECT
        e.id as eid,
        ts.name AS stage_name,
        DATE_FORMAT(e.startdate, '%d.%m.%Y') AS startdate,
        DATE_FORMAT(e.startdate, '%H:%i') AS starttime,
        MIN(IF( ep.number = 1, p.name, NULL)) AS home_team,
        IF(e.status_type = 'notstarted', '-',(MIN(IF(ep.number = 1, r.value, NULL)))) AS home_score,
        IF(e.status_type = 'notstarted', '-',(MIN(IF(ep.number = 2, r.value, NULL)))) AS away_score,
        MIN(IF(ep.number = 2, p.name, NULL)) AS away_team,
        es.name AS status_text
FROM
        tournament_template AS tt INNER JOIN
        tournament AS t ON t.tournament_templateFK = tt.id INNER JOIN
        tournament_stage AS ts ON t.id = ts.tournamentFK INNER JOIN
        event AS e ON ts.id = e.tournament_stageFK INNER JOIN
        event_participants AS ep ON e.id = ep.eventFK LEFT JOIN
        status_desc AS es ON e.status_descFK = es.id LEFT JOIN
        participant AS p ON ep.participantFK = p.id LEFT JOIN
        result AS r ON ep.id = r.event_participantsFK AND r.result_code = 'runningscore' LEFT JOIN
        property AS prop ON e.id = prop.objectFK AND prop.object ='event' AND prop.name = 'Live'
WHERE
        tt.sportFK = '1'
GROUP BY
        e.id
ORDER BY 
        ts.id, e.startdate, e.id

现在我的问题是我可以在 where 子句中使用 home_team 和 away_team 吗？如果是那么如何。就像理论上我想要在 where 子句中一样。

WHERE home_team = 'Navibank Saigon' 或 away_team = 'Navibank Saigon' ;

但它给出了未知列错误。

请帮忙

Answer 1

看起来 p.name 是作为 home_team 返回的内容的真实列名称，所以让您的 WHERE 子句像

WHERE p.name = 'Navibank Saigon'