我有一个存储关系的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/