这个问题已经回答了好几次了，但我就是没法解决。我使用了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
“assist”列显示与“goal”列相同的值。
行pma.activity_id='1'仅选择目标。
如何设置“协助”列使用与“目标”列完全相同的条件，但不使用pma.activity\u id='1'我想将其更改为'2'？
最佳答案
您可以将另一个连接添加到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。