mysql - 两个查询在同一个数据库中给出不同的结果

Question

请帮忙。我使用的是 MySQL 5.1.30 社区版。

我有四个表:nts、operator、country、cooperationtype

• 表 `nts` 有一列(`operatorId`)是表 `operator` 中列 `id` 的外键，还有一列(`voice`)是表中 `id` 列的外键合作类型
• 表操作符有一列(`country_id`)是表country中列(`id`)的外键

我想获取所有 voice 值不等于“N/A”的运营商和国家/地区的计数，并使用此查询按 cooperationtype.id 对它们进行分组:

SELECT cooperationtype.id AS cooptype,
COUNT(DISTINCT country_id) AS country, COUNT(DISTINCT operatorId) AS operator
FROM nts INNER JOIN operator ON operator.id = nts.operatorId INNER JOIN country ON operator.country_id = country.id
INNER JOIN cooperationtype ON cooperationtype.id = nts.voice
WHERE cooperationtype.code <> 'N/A' GROUP BY cooperationtype.id

我得到了这个结果:

cooptype   country    operator1         128          3482         11           113         15           17

The sum of this query is 154 countries and 376 operators.

But then when I want to get all of the counts of operators and countries where all of the value of voice not equals to 'N/A', regardless the of cooperationtype.id with this query:

SELECT COUNT(DISTINCT country_id) AS country, COUNT(DISTINCT operatorId) AS operator
FROM nts INNER JOIN operator ON operator.id = nts.operatorId INNER JOIN country ON operator.country_id = country.id
INNER JOIN cooperationtype ON cooperationtype.id = nts.voice
WHERE cooperationtype.code <> 'N/A'

I got this result:

country operator133     372

My questions are:

1. Why is the sum of the result from the first query doesn't equal to the result from the second query?
2. Which one is the right result?

Data example:

voice     country   operator
1         US        1  
1         US        2  
1         UK        3  
1         UK        4  
2         US        1  
2         US        2  

对于第一个查询，数据应该生成:

cooptype  country   operator 
1         2         4 
2         2         2 

对于第二个查询，数据应该生成:

country   operator 
2         4 

Answer 1

Why is the sum of the result from the first query doesn't equal to the result from the second query?

因为你使用了COUNT(DISTINCT)。

它按组计算不同的记录。

您的第一个查询对具有相同国家但不同 cooptype 的两条记录计数两次(因为它按 cooptype 分组)，而第二个查询对它们计数一次。

Which one is the right result?

都对。

对于给定的数据:

cooptype  country
1         US
1         US
1         UK
1         UK
2         US
2         US

第一个查询将返回:

1         2
2         1

第二个会返回

2

，因为你有:

• 2 cooptype = 1 中的不同国家(美国 和英国)
• 1 cooptype = 2 中的不同国家(美国)
• 2 个不同的国家/地区(美国 和英国)

在您对“正确”的定义中，哪个是“正确”取决于这个定义。

如果您只想让第二个查询匹配第一个查询的结果，请使用

SELECT  COUNT(DISTINCT cootype, country_id) AS country,
        COUNT(DISTINCT cooptype, operatorId) AS operator
FROM    nts
INNER JOIN
        operator
ON      operator.id = nts.operatorId
INNER JOIN
        country
ON      operator.country_id = country.id
INNER JOIN
        cooperationtype
ON      cooperationtype.id = nts.voice
WHERE   cooperationtype.code <> 'N/A'

但是，同样，这可能与您的第一个查询一样错误。

对于这些数据:

cooptype  country  operator
1         US       1
1         US       1
1         UK       2
1         UK       2
2         US       1
2         US       1

，什么是正确的结果集？