mysql - 我必须查询两次才能得到这个结果吗？

Question

SQL 新手。我有 3 个表:games、invitations 和 users

此查询返回正在进行的游戏:

SELECT games.* 
FROM   games 
       INNER JOIN invitations 
               ON invitations.game_id = games.id 
                  AND invitations.user_id = 1 
                  AND invitations.status = 1 
                  AND games.status = 1; 

此查询返回已完成的游戏:

SELECT games.* 
FROM   games 
       INNER JOIN invitations 
               ON invitations.game_id = games.id 
                  AND invitations.user_id = 1 
                  AND invitations.status = 1 
                  AND games.status = 2; 

此查询返回用户尚未决定是否加入的游戏:

SELECT games.* 
FROM   games 
       INNER JOIN invitations 
               ON invitations.game_id = games.id 
                  AND invitations.user_id = 1 
                  AND invitations.status = 0;

我想知道，如果我有一个网络应用程序需要这 3 个查询的结果，我是否必须执行 3 条 sql 语句，或者有没有办法将它们压缩为 1 条？提出 3 个请求对我来说似乎太多了，这是处理此问题的正常方法吗？

Answer 1

试试这个

SELECT 
case when invitations.status = 1 
 then case 
        when games.status = 1 then 'progress'
        when games.status = 2 then 'finished'
      end
else 'not decided' end as game_status,
games.* 
FROM   games 
       INNER JOIN invitations 
               ON invitations.game_id = games.id 
                  AND invitations.user_id = 1 
                  AND (invitations.status = 0 or 
                        (invitations.status = 1 and games.status in (1,2)))

SQL DEMO