mysql - 根据case情况执行不同的查询

Question

我有这个查询:

SELECT r.runk_user_id,
       CASE r.runk_user_id WHEN g.group_player1_user_id THEN 'player 1'
                           WHEN g.group_player2_user_id THEN 'player 2'
                           WHEN g.group_player3_user_id THEN 'player 3'
                           WHEN g.group_player4_user_id THEN 'player 4'
                           ELSE 'no player matches'
       END AS player
FROM   groups g
JOIN   runks r ON g.group_id = r.runk_group_id

这将输出以下结果:

我需要它根据 case 语句的结果执行其他查询，而不是输出该结果。

因此，如果“玩家 1”是这种情况，则需要执行此查询:

UPDATE groups AS g1, 
(SELECT (runk_image_rating_total + runk_caption_rating_total + runk_challenge_rating_total) as total, g.group_id
FROM groups g
JOIN runks r
ON g.group_id = r.runk_group_id) AS g2
SET g1.group_player1_standing = g2.total + g1.group_player1_standing
WHERE g1.group_id = g2.group_id

如果是“玩家 2”，则需要执行:

UPDATE groups AS g1, 
(SELECT (runk_image_rating_total + runk_caption_rating_total + runk_challenge_rating_total) as total, g.group_id
FROM groups g
JOIN runks r
ON g.group_id = r.runk_group_id) AS g2
SET g1.group_player2_standing = g2.total + g1.group_player2_standing
WHERE g1.group_id = g2.group_id

这就是表格的样子:

等等等等。谁能告诉我该怎么做？

Answer 1

我曾经不得不使用 CASE 做类似的事情陈述。我尝试调整您的代码，请告诉我它是否有效

UPDATE groups AS g1, 
(SELECT (runk_image_rating_total + runk_caption_rating_total + runk_challenge_rating_total) as total, g.group_id
FROM groups g
JOIN runks r
ON g.group_id = r.runk_group_id) AS g2
SET g1.group_player2_standing = CASE
WHEN player = player_1 then g2.total + g1.group_player1_standing
WHEN player = player_2 then g2.total + g1.group_player2_standing
ELSE ''
END 
WHERE g1.group_id = g2.group_id AND player IN (player_1, player_2)