MySQL计数(不同的())意想不到的结果MySQL COUNT(DISTINCT()) unexpected results

- 此内容更新于:2014-12-30
主题:

原文:

I'm using MySQL 5.0.45 on CentOS 5.1.

SELECT DISTINCT(email) FROM newsletter

Returns 217259 rows

SELECT COUNT(DISTINCT(email)) FROM newsletter

Returns 180698 for the count.

SELECT COUNT(*) FROM (SELECT DISTINCT(email) FROM newsletter) AS foo

Returns 180698 for the count.

Shouldn't all 3 queries return the same value?

Here is the schema of the newsletter table

CREATE TABLE `newsletter` (
  `newsID` int(11) NOT NULL auto_increment,
  `email` varchar(128) NOT NULL default '',
  `newsletter` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`newsID`)
) ENGINE=MyISAM;

Update: I've found that if I add a WHERE clause to the first query then I get the correct results. The WHERE clause is such that it will not effect the results.

SELECT DISTINCT(email) FROM newsletter WHERE newsID > 0

dkretz的回复:良好的规范问题!

(原文:Good self-answered question!)

解决方案:
你可以相交的不同结果,看看额外的行吗?
原文:

could you possibly intersect the differing results to see what the extra rows are?

mysql支持减去吗?

(原文:Does mysql support MINUS?)

Dave Costa的回复:的一个不同的结果是一个集合的,他怎么能这样做呢?

(原文:One of the differing results is an aggregate, how could he do this?)

解决方案:
我的猜测是,有一些邮件列中的null值。试一试
原文:

My guess is that there are some null values in email column. Try

select count(*) from newsletter where email is null;
KernelM的回复:列可以# 39;t null。

(原文:The column can't have nulls.)

Dave Costa的回复:此外,最多应该导致的差异。

(原文:Moreover, at most that should result in a difference of 1.)

Kevin Collins的回复:也寻找空字符串值,欺骗很多新手开发者使用,以避免非NULL条款$ string = & # 39;& # 39;

(原文:also look for empty string values, a cheat lots of newbie devs use to avoid the NOT NULL clause $string = '')

解决方案:
在第一种情况下,如果你改变它”选择不同的从通讯订单通过电子邮件(邮件);“我不认为它应该发挥作用,但它可能。
原文:

In the first case, what happens if you change it to "select distinct(email) from newsletter order by email;" I don't think it should make a difference, but it might.

解决方案:
是的,正如Maglob说你在电子邮件中可以得到一些null列。试的时候可以这样说: 这将给你你希望的结果。哦,等等。定义说,电子邮件不接受NULL值。如果真的是这样,那么奇怪。
原文:

Yes, as Maglob said you may be getting some NULLs in your email column. Try with something like:

SELECT COUNT(COALESCE(email, 0)) FROM newsletter

That would give you the results you expect. Oh, wait. The definition says that email does not accept NULL values. If that is truly the case, then it is odd.

解决方案:
一个可能的解释是,行之间从表中删除两个查询。但我想你已经多次运行这些不同的订单所以我怀疑的。 你从什么rowcount这个查询:
原文:

One possible explanation is that rows were deleted from the table between your two queries. But I assume you've run these multiple times in various orders so I doubt that's it.

What rowcount do you get from this query:

SELECT email FROM newsletter GROUP BY email;