仔细查看你的查询中的WHERE
子句,就能明白问题所在。
你的查询用文字表达为:
从...中选择...
WHERE 主队得分 = (作为主队时巴基斯坦的最大得分,即9分)
所以你的查询返回的是所有主队得分为9分的记录,而没有检查具体的国家。尽管你已经使用了home_country
条件,但这还不够。
请使用以下查询:
SELECT date, home_team, away_team, home_score, away_score
FROM `my-practice-project-394200.football_results.scores`
WHERE home_score = (SELECT MAX(home_score)
FROM `my-practice-project-394200.football_results.scores`
WHERE home_team = "Pakistan")
AND home_team = "Pakistan"
或者你可以使用NOT EXISTS
方法:
SELECT date, home_team, away_team, home_score, away_score
FROM `my-practice-project-394200.football_results.scores` AS t
WHERE NOT EXISTS (
SELECT 1 FROM `my-practice-project-394200.football_results.scores` AS tt
WHERE t.home_team = tt.home_team AND tt.home_score > t.home_score
)
AND home_team = "Pakistan"