mysql select as 多个_mysql select语句有多个where / conditions

我在mysql中有以下两个表：

用户：

+--------+-----------+

| userId | userName |

+--------+-----------+

| 1 | magnus |

| 2 | fabiano |

| 3 | alexander |

| 4 | veselin |

+--------+-----------+

游戏：

+--------+---------+---------+

| gameId | userId1 | userId2 |

+--------+---------+---------+

| 1 | 1 | 2 |

| 2 | 1 | 3 |

| 3 | 2 | 3 |

| 4 | 2 | 4 |

+--------+---------+---------+

我怎样才能构建一个单一的查询,这样我就可以得到以下输出：fabiano的反对者：

输出：

+--------+-----------+

| gameId | userName |

+--------+-----------+

| 1 | magnus |

| 3 | alexander |

| 4 | veselin |

+--------+-----------+

EDIT1：

这就是我的尝试,我无法将它们放入单个查询中：

>选择fabiano的对手[选择*来自2中的游戏(userId1,userId2);]

>读取每一行,并检查它们中的哪一个是fabiano(2),然后选择另一个userId

>从这些对手的userIds中,从用户表中获取他们的名字

EDIT2：

受以下答案的启发,我写了这个(他们的工作)：

-- NO JOIN

select x.gameId, users.userName from

(

select gameId, userId2 as id from games where userId1=2

UNION

select gameId, userId1 as id from games where userId2=2

) as x, users

where users.userId = id;

-- NO JOIN, NO UNION

select x.gameId, users.userName from (

SELECT g.gameId,

CASE WHEN userId1 = 2

THEN userId2

WHEN userId2 =2

THEN userId1

END AS id

FROM games g) as x, users

where users.userId = id;

解决方法:

您可以将两组数据合并在一起,即Fabiano为User 1的所有游戏,以及他扮演用户2角色的所有游戏：

SELECT x.Opponent

FROM

(

SELECT u.Name AS Opponent

FROM games g

INNER JOIN users u

ON g.userId2 = u.UserId

WHERE g.UserId1 = 2 -- Fabiano

UNION

SELECT u.Name

FROM games g

INNER JOIN users u

ON g.userId1 = u.UserId

WHERE g.UserId2 = 2 -- Fabiano

) AS x;

此时假设法比亚诺不能同时兼顾User1和User2,因为我们需要考虑UNION ALL vs UNION DISTINCT

标签：sql,mysql,select

来源： https://codeday.me/bug/20190728/1560239.html