MySQL 连接多个 SELECT 查询

Question

我尝试使用 UNION 连接以下 SELECT 语句，但最终得到同一列上的所有结果。但是，我希望每个查询的结果都位于单独的列中。

SELECT Player_id,Game_id, R1
FROM poker_hands f, poker_cards a, poker_cards b
WHERE f.r1 = a.card_name 
AND f.r2 = b.card_name 
AND (f.hand_type = 'Four of kind' 
AND (a.game_value != b.game_value)
UNION ALL
/* R2 */
SELECT Player_id,Game_id, R2
FROM poker_hands f, poker_cards a, poker_cards b
WHERE f.r1 = a.card_name 
AND f.r2 = b.card_name 
AND (f.hand_type = 'High hand'
AND (a.game_value != b.game_value);

我想要类似的输出

Player_id | Game_id | R1 | R2
0001      | 0002    |    | 8S
0002      | 0003    | 9H |
0003      | 0004    | 5C | 5S
0004      | 0005    |    | 6D

Answer 1

你可以使用case statement，尝试这样:

SELECT Player_id,Game_id, CASE WHEN f.hand_type = 'Four of kind' THEN R1 ELSE '' END as R1,
CASE WHEN f.hand_type = 'High hand' THEN R2 ELSE '' END AS R2
FROM poker_hands f, poker_cards a, poker_cards b
WHERE f.r1 = a.card_name 
AND f.r2 = b.card_name 
AND (a.game_value != b.game_value AND (f.hand_type = 'High hand' OR f.hand_type = 'Four of kind' ));