SELECT DISTINCT不返回distinct值

Question

我使用以下MYSQL来获取不同的emails.to值，但请参见下面的结果

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, `emails`.`time` as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = 'sdadr@googlemail.com') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

-

to    fromEmail             timestamp
370   sdadr@googlemail.com  2012-08-15 16:52:15
1923  sdadr@googlemail.com  2012-08-15 10:54:48
890   sdadr@googlemail.com  2012-08-15 10:43:06
890   sdadr@googlemail.com  2012-08-15 10:43:02

为什么它返回一个重复的890？

Answer 1

因为时间戳不同。DISTINCT用于整行，而不仅仅是一列。

这会将您的时间戳截断为日期，然后890将不会重复。就像Nikola Markovinović建议的那样。

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, DATE(`emails`.`time`) as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = 'sdadr@googlemail.com') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

如果你只想截断秒数，这也会得到你想要的结果，你可以这样做：

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, 
DATE_FORMAT(`emails`.`time`, '%Y-%m-%d %H:%i') as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = 'sdadr@googlemail.com') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

Manual entry for DATE_FORMAT()获取更多信息。

根据您的评论，如果您只想拥有唯一的emails.to，只需选择该列，不再使用DISTINCT。

SELECT DISTINCT `emails`.`to`
FROM `emails` 
WHERE ((`emails`.`from` = 'sdadr@googlemail.com') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

Answer 2

如上所述，截断到日期：

SELECT DISTINCT `emails`.`to`,`emails`.`from` as fromEmail, DATE(`emails`.`time`) as timestamp
FROM `emails` 
WHERE ((`emails`.`from` = 'sdadr@googlemail.com') 
OR (`emails`.`to` = '887')) 
AND (`emails`.`to` != 0) 
ORDER BY `id` DESC LIMIT 4

Answer 3

SELECT DISTINCT `emails`.`to` 
  FROM `emails`  WHERE ((`emails`.`from` = 'sdadr@googlemail.com')  OR (`emails`.`to` = '887'))  AND (`emails`.`to` != 0)  
  ORDER BY `id` DESC LIMIT 4 

根据您的问题，您正在寻找不同的to地址，您可以从该查询中获得它。