[sql情况什么时候]为什么我同时指定两个条件时何时无法工作？

Question

嗨，我正在尝试从Datacamp给出的其他版本的SQL查询。

我提供的查询是

SELECT 
    date,
    -- Identify the home team as Barcelona or Real Madrid
    case when hometeam_id = 8634 then 'FC Barcelona' 
        else 'Real Madrid CF' end as home,
    -- Identify the away team as Barcelona or Real Madrid
    case when awayteam_id = 8634 then 'FC Barcelona' 
        else 'Real Madrid CF' end as away
FROM matches_spain
WHERE (awayteam_id = 8634 OR hometeam_id = 8634)
      AND (awayteam_id = 8633 OR hometeam_id = 8633);

我试图调整它
思考：“与其为每种情况方案设置一个条件，让我们通过将两个条件与'和连接起来同时设置两个条件”
我的查询如下。

select date,
case when hometeam_id=8634 and awayteam_id = 8633 then 'FC Barcelona'
     when hometeam_id=8633 and awayteam_id = 8634 then 'Real Madrid CF'
     end as home,
case when hometeam_id=8633 and awayteam_id = 8634 then 'Real Madrid CF'
     when hometeam_id=8634 and awayteam_id = 8633 then 'FC Barcelona'
     end as away
from matches_spain
where (hometeam_id=8634 and awayteam_id=8633)
or (hometeam_id=8633 and awayteam_id=8634)

teams_spain表就像这样

和

matches_spain表就像这样

“ Matches_spain”是一些赛季中足球比赛的记录，并带有主队ID和Awary Team ID。每个团队都有自己独特的ID，并且可以在Teams_Spain表中找到独特的ID。

为什么我的替代代码打印错误的输出？

理想的输出为 我错误的输出是

Answer 1

除了我不明白您为什么要像自己一样更改逻辑的事实外，当构建构造的主队和客队团队时，您都会使用对他们的认同结果。第二个情况不正确。您写道：

case when hometeam_id=8633 and awayteam_id = 8634 then 'Real Madrid CF'
 when hometeam_id=8634 and awayteam_id = 8633 then 'FC Barcelona'
 end as away

...反之亦然：

case when hometeam_id=8634 and awayteam_id = 8633 then 'Real Madrid CF'
 when hometeam_id=8633 and awayteam_id = 8634 then 'FC Barcelona'
 end as away