mysql - 为什么我通过子查询的结果得到错误组？

Question

我想查找具有相同电话、project_id、create_time、trace_content 的跟踪记录的数量

但是这个sql:

select count(1) from trace WHERE  DATE(create_time)="2020-01-16" AND 
  id not in (
     select id from trace WHERE DATE(create_time)="2020-01-16"  
     GROUP BY telephone,project_id,create_time,trace_content
);

返回错误结果。如果我使用:

select count(1) from trace WHERE  DATE(create_time)="2020-01-16" AND 
    id not in (
               select id FROM (
                      select id from trace WHERE DATE(create_time)="2020-01-16"  
                      GROUP BY telephone,project_id,create_time,trace_content
              ) a
);

返回正确的结果。Mysql版本是5.6.26谁能告诉我原因吗？

例如，该表包含以下数据:

1. id = 1、project_id = 1、电话 = 1、trace_contest = 测试、creatime = 2020-01-16 18:27:46

2. id = 2、project_id = 1、电话 = 1、trace_contest = 测试、creatime = 2020-01-16 18:27:46

3. id = 3、project_id = 2、电话 = 2、trace_contest = test1、creatime = 2020-01-16 18:22:27

第一个sql返回0，第二个sql返回1

Answer 1

您的两个查询实际上都是错误的:您选择的是 id 但它不是 group by 子句的一部分，因此您基本上会得到一个随机值群组。在大多数 RDBMS(以及 MySQL 的非古老版本)中，这会生成语法错误。

我了解您想要计算表中有多少(telephone、project_id、create_time、trace_content)出现多次。

您可以为此使用两个级别的聚合:

select count(*) cnt_duplicates
from (
    select 1
    from mytable
    where create_time >= '2020-01-16' and create_time < '2020-01-17'
    group by telephone, project_id, create_time, trace_content
    having count(*) > 1
) t

内部查询对记录进行分组，并对出现多个的记录进行过滤。外部查询计算内部查询返回的记录数。

请注意，我重写了您的日期条件，以便它可以利用 create_time 上的索引(如果有)。