我有一个存储关系的mysql表。项目可以在一个方向上与另一个项目相关,或者两个项目都可以彼此相关。
我想返回与我的主项相关的所有项-但我还想检查相关项是否与当前项有“反向关系”,并将其显示为布尔值

 |--------------|---------------|
 |    SKU       |  related_SKU  |
 |--------------|---------------|
 | 0001         |  0099         |
 | 0002         |  0099         |
 | 0099         |  0001         |
 |--------------|---------------|

如果我想得到SKU=0001的所有关系
SELECT related_SKU from relationships where SKU='0001'

回报
 |--------------|
 | related_SKU  |
 |--------------|
 | 0099         |
 |--------------|

但我想要的是
 |--------------|---------------|
 | related_SKU  |   reciprocal  |
 |--------------|---------------|
 | 0099         |      1        |
 |--------------|---------------|


 SELECT related_SKU from relationships where SKU='0002'

 |--------------|---------------|
 | related_SKU  |   reciprocal  |
 |--------------|---------------|
 | 0099         |      0        |
 |--------------|---------------|

最好的方法是什么?

最佳答案

你可能想试试这样的方法:

SELECT r1.related_SKU,
       IF(( SELECT COUNT(*)
            FROM   relationships r2
            WHERE  r2.SKU = r1.related_SKU AND r2.related_SKU = r1.SKU
       ) > 0, 1, 0) AS reciprocal
FROM   relationships r1
WHERE  r1.SKU = '0001';

测试用例:
CREATE TABLE relationships (SKU int, related_SKU int);

INSERT INTO relationships VALUES (1, 99);
INSERT INTO relationships VALUES (2, 99);
INSERT INTO relationships VALUES (99, 1);

倒数结果:
SELECT r1.related_SKU,
       IF(( SELECT COUNT(*)
            FROM   relationships r2
            WHERE  r2.SKU = r1.related_SKU AND r2.related_SKU = r1.SKU
       ) > 0, 1, 0) AS reciprocal
FROM   relationships r1
WHERE  r1.SKU = '0001';

+-------------+------------+
| related_SKU | reciprocal |
+-------------+------------+
|          99 |          1 |
+-------------+------------+
1 row in set (0.00 sec)

无倒数结果:
SELECT r1.related_SKU,
       IF(( SELECT COUNT(*)
            FROM   relationships r2
            WHERE  r2.SKU = r1.related_SKU AND r2.related_SKU = r1.SKU
       ) > 0, 1, 0) AS reciprocal
FROM   relationships r1
WHERE  r1.SKU = '0002';

+-------------+------------+
| related_SKU | reciprocal |
+-------------+------------+
|          99 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

关于sql - 在mysql中检查对等关系。一个简单的一 table 问题,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2956493/

10-10 13:54
查看更多