mysql - SQL 查询重复结果

Question

我正在做一个使用 MySQL 5 的项目。要求如下:

Give the user names, device types, OS version and fruit involved in picks, where users had thesame device type, were running iOS 4 or 4.1, and picked the same fruit

相关表格如下:

User: {uID: INT, name: VARCHAR(45), deviceOS: VARCHAR(45), deviceType: VARCHAR(45)}

Pick: {uID: INT, ts: TIMESTAMP, fruit: VARCHAR(45)}

(Primary keys in bold. uID in Pick is a foreign key of uID in User.)

我正在做以下查询:

SELECT DISTINCT NAME1, OS1, DEV1, NAME2, OS2, DEV2, P1.fruit FROM Pick AS P1, Pick AS P2, 

(SELECT U1.uID AS User1, U1.name AS NAME1, U1.deviceOS AS OS1, U1.deviceType AS DEV1, 
        U2.uID AS User2, U2.name AS NAME2, U2.deviceOS AS OS2, U2.deviceType AS DEV2
FROM User AS U1, User AS U2 
WHERE (U1.uID != U2.uID) AND
      (U1.deviceType = U2.deviceType) AND
      (U1.deviceOS = "4" OR U1.deviceOS = "4.1") AND
      (U2.deviceOS = "4" OR U2.deviceOS = "4.1")) AS PartialResult

WHERE (P1.uID = PartialResult.User1) AND
      (P2.uID = PartialResult.User2) AND
      (P1.fruit = P2.fruit)

这将返回以下结果，但如您所见，它在某种程度上是“重复的”:

我已经尝试使用 GROUP BY fruit 解决这个问题，但在一般情况下它不会返回正确的结果。限制 1 也不适用于一般情况。因此，经过数小时的尝试，我必须问:

有没有办法在一般情况下防止这种重复？

谢谢!

Answer 1

代替 U1.uID != U2.uID，编写 U1.uID > U2.uID。