mysql - SELECT 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()欲了解更多信息。

根据您的评论，如果您只想拥有唯一的电子邮件，只需选择该列即可，无需再使用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