MySQL的获取上不相对于另一个值给定的标准

Question

我有一个表像下存在的行：

+-----+-------+ 
| uid | value | 
+-----+-------+ 
| 104 | L | 
| 104 | M | 
| 104 | N | 
| 103 | Q | 
| 101 | N | 
| 102 | L | 
+-----+-------+ 

现在我所寻找的是获得104的价值和它的比较其他uid和如果只有返回值没有找到，如下列：

+-----+-------+ 
| uid | value | 
+-----+-------+ 
| 101 | L | 
| 101 | M | 
| 102 | M | 
| 102 | N | 
| 103 | L | 
| 103 | M | 
| 103 | N | 
+-----+-------+ 

这里，104有L，M和N，所以我找不具备L，M等的UID的值或N（相对于104的值），请注意，103得到Q，但由于它不在104，我试图与104比较，Q在这里不相关，ho W在MySQL中获取？

Answer 1

请尝试以下基于CROSS JOIN和LEFT JOIN的查询。

SELECT 
DISTINCT finalTable.uid, 
finalTable.`value` 
FROM 
(SELECT 
    YOUR_TABLE.uid, 
    t.`value` 
FROM YOUR_TABLE 
CROSS JOIN (
     SELECT 
      * 
     FROM YOUR_TABLE 
     WHERE uid = 104 
    ) t 
WHERE YOUR_TABLE.uid != 104 
AND t.`value` <> YOUR_TABLE.`value` 
) finalTable 
LEFT JOIN YOUR_TABLE ON YOUR_TABLE.uid = finalTable.uid AND YOUR_TABLE.`value` = finalTable.`value` 
WHERE YOUR_TABLE.uid IS NULL 
ORDER BY finalTable.uid; 

见Demo Here

Answer 2

SELECT DISTINCT a.uid 
      FROM my_table a 
      LEFT 
      JOIN my_table b 
      ON b.value <> a.value 
      AND b.uid = 104 
      WHERE a.uid <> 104 
      AND b.uid IS NULL 

或者类似的东西