mysql - mysql 查询子句中反之亦然

Question

我想根据两个条件选择记录

  SELECT *
  FROM record
  WHERE FromPersonID in (1,2) AND
  ToPersonID in (1,2)

但是这个查询可以给我这样的记录:

 +--------+----------------+----------------
 |  id    |  FromPersonID  |   ToPersonID |
 +--------+----------------+----------------
     1             1              2
     2             1              1
     3             2              2
     4             2              1
     5             1              2

在id 2和3中，该记录的FromPersonID和ToPersonID具有相同的值

我想要的是它只会检查两个字段之间的反之亦然。

任何机构都可以帮助实现这一目标吗？

谢谢。

Answer 1

如果你想获得

     2             1              1
     3             2              2


SELECT *
  FROM record
  WHERE FromPersonID in (1,2) AND
  ToPersonID in (1,2) and FromPersonID=ToPersonID;

否则，如果您想获取这两列具有不同值的记录

SELECT *
      FROM record
      WHERE FromPersonID in (1,2) AND
      ToPersonID in (1,2) and FromPersonID!=ToPersonID;



SELECT * FROM TT;
+------+--------------+------------+
| ID   | FromPersonID | TOPERSONID |
+------+--------------+------------+
|    1 |            1 |          2 |
|    2 |            1 |          1 |
|    3 |            2 |          2 |
|    4 |            2 |          1 |
|    5 |            1 |          2 |
+------+--------------+------------+
5 rows in set (0.00 sec)

 SELECT *       FROM TT       WHERE FromPersonID in (1,2) AND  TOPERSONID in (1,2) and FromPersonID!=TOPERSONID;
+------+--------------+------------+
| ID   | FromPersonID | TOPERSONID |
+------+--------------+------------+
|    1 |            1 |          2 |
|    4 |            2 |          1 |
|    5 |            1 |          2 |
+------+--------------+------------+
3 rows in set (0.00 sec)