sql - 在不同条件下两次检索同一列

Question

这个问题已经回答了多次，但我就是无法让它发挥作用。我使用了来自 this 的一些答案问题，但我总是“得到用作表达式的子查询返回的多于一行的错误”

我有以下 sql 查询:

SELECT DISTINCT p.name, pma.time AS  goal, pma.time AS assist 

FROM player p

INNER JOIN player_match pm
ON p.player_id = pm.player_id

INNER JOIN matches m
ON m.match_id = pm.match_id

INNER JOIN team_match tm
ON tm.team_id = p.team_id 

FULL JOIN player_match_activity pma
ON pma.player_id = p.player_id
AND pma.activity_id = '1'
AND pma.match_id = m.match_id

WHERE m.match_id = '163'
AND tm.home_away = 'home'

查询给了我以下结果:

 name    |    goal    |    assist
-------------------------------------
Ronaldo         1            1
Messi           3            3
Vardy  

“助攻”列显示与“目标”列相同的值。行 pma.activity_id = '1' 仅选择目标。

如何设置“协助”列使用与“目标”列完全相同的条件，但我想将其更改为“2”而不是 pma.activity_id = '1'？

Answer 1

您可以向 player_match_activity 表添加另一个联接，或者您可以将 pma.activity_id = '1' 更改为 pma.activity_id IN ('1' ,'2') 并使用 CASE 表达式来选择填充正确的列:

SELECT DISTINCT p.name, pma_goal.time AS  goal, pma_assist.time AS assist 
FROM player p
INNER JOIN player_match pm
   ON p.player_id = pm.player_id
INNER JOIN matches m
   ON m.match_id = pm.match_id
INNER JOIN team_match tm
   ON tm.team_id = p.team_id 
FULL JOIN player_match_activity pma_goal
   ON pma_goal.player_id = p.player_id
   AND pma_goal.activity_id = '1'
   AND pma_goal.match_id = m.match_id    
FULL JOIN player_match_activity pma_assist
   ON pma_assist.player_id = p.player_id
   AND pma_assist.activity_id = '2'
   AND pma_assist.match_id = m.match_id    
WHERE m.match_id = '163'
  AND tm.home_away = 'home'

或者:

SELECT p.name, MAX(CASE WHEN pma.activity_id = '1' THEN pma.time END) AS  goal
             , MAX(CASE WHEN pma.activity_id = '2' THEN pma.time END) AS  assist
FROM player p
INNER JOIN player_match pm
   ON p.player_id = pm.player_id
INNER JOIN matches m
   ON m.match_id = pm.match_id
INNER JOIN team_match tm
   ON tm.team_id = p.team_id 
FULL JOIN player_match_activity pma
   ON pma.player_id = p.player_id
   AND pma.activity_id IN ('1','2')
   AND pma.match_id = m.match_id    
WHERE m.match_id = '163'
  AND tm.home_away = 'home'
GROUP BY p.name

此外，不确定您是否需要在此处使用 FULL JOIN。