mysql - 当只知道一个值时从同一列获取不同的值

Question

我有下表

     +-----+-------+
     |col 1| col 2 |
     +-----+-------+
     |   a |   b   |
     |   b |   a   |
     |   a |   d   |
     |   c |   b   |

我知道 a 的值，现在我想使用该值运行查询来获取 id，其中 a = b 且 b = a...我期望结果为

     +-----+-------+
     |col 1| col 2 |
     +-----+-------+
     |   a |   b   |
     |   b |   a   |

谢谢

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(col1 CHAR(1) NOT NULL
,col2 CHAR(1) NOT NULL
,PRIMARY KEY(col1,col2)
);

INSERT INTO my_table VALUES
('a','b'),
('b','a'),
('a','d'),
('c','b');

SELECT x.* 
  FROM my_table x 
  JOIN my_table y 
    ON y.col1 = x.col2 
   AND y.col2 = x.col1 
 WHERE 'a' IN (x.col1,x.col2);
+------+------+
| col1 | col2 |
+------+------+
| b    | a    |
| a    | b    |
+------+------+
2 rows in set (0.00 sec)