有条件地选择MYSQL列 [英] Selecting MYSQL column conditionally
问题描述
我的问题的示例架构在 SQLFiddle 中给出,如下所示:
Example schema for my question is given at SQLFiddle and as follow:
CREATE TABLE `players` (
`player1id` int(11) NOT NULL,
`player2id` int(11) NOT NULL,
PRIMARY KEY (`player1id`,`player2id`)
) ENGINE=InnoDB;
INSERT INTO `players` values
(1,5),
(1,7),
(5,3),
(5,4),
(2,1);
在在线游戏中,我想选择对手,该对手可以在玩家player1id
或player2id
中.
In a online game, I want to select opponent, which could be in player player1id
or player2id
.
需要示例输入/输出
Input 1: Output 5, 7, 2
Input 5: Output 1, 3 ,4
& so on.
也就是说,所需的数据可以在任何列中,但是我需要有条件地或以任何其他方式在单列中输出.我听说过MySQL条件列,但无法创建查询以获取所需的输出.有人可以帮忙进行必要的查询吗?
That is, required data could be in any column but I need output in single column conditionally or any other way. I heard about MySQL conditional column but could not create query to get required output. Can someone please help with required query.
修改
基于此链接,我运行以下命令查询,但失败.
Based on this link, I run following query but failed.
SELECT IF(PLAYER1ID IS 1,PLAYER2ID as opponent,PLAYER1ID as opponent)
FROM players
WHERE PLAYER1ID = 1 OR PLAYER2ID = 1;
推荐答案
我认为您可以使用case when then
语法,如下所示:
I think you may use case when then
syntax as below:
SELECT CASE WHEN player1id = 1 THEN player2id ELSE player1id END
FROM players WHERE player1id =1 OR player2id=1;
或者:
SELECT CASE WHEN player1id = 1 THEN player2id WHEN player2id =1 THEN player1id END
FROM players WHERE player1id =1 OR player2id=1;
这篇关于有条件地选择MYSQL列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!