mysql - 在特定日期添加记录时，如何显示组中所有潜在的重复匹配项

Question

我在为此查询提出逻辑解决方案时遇到问题

假设我有这个数据集

| id | firstname | lastname | productionunit |           lastmodifieddate |
|----|-----------|----------|----------------|----------------------------|
|  1 |      John |    Smith |          UnitA | December, 29 2016 00:00:00 |
|  2 |    Rachel |    Smith |          UnitA | December, 31 2016 00:00:00 |
|  3 |      Abby |   Turner |          UnitA |  January, 04 2017 00:00:00 |
|  4 |       Sam |    Telly |          UnitA | December, 29 2016 00:00:00 |
|  5 |       Mac |    Telly |          UnitB | December, 30 2016 00:00:00 |
|  6 |   Lincoln |    Telly |          UnitB |  January, 01 2017 00:00:00 |
|  7 |       Sam |    Telly |          UnitC | December, 30 2016 00:00:00 |
|  8 |      Test |    Smith |          UnitD | December, 30 2016 00:00:00 |

只要 ProductionUnit 中的 LastName 相同，我就需要查找重复项。

如果其中一条记录已于 2016 年 12 月 30 日添加，我只需要这些重复项组

这是我写的查询

SELECT
        a.id,
        a.firstname,
        a.lastname,
        a.productionunit,
        a.lastmodifieddate
    FROM forgerock a
    JOIN (SELECT
            productionunit ,lastname,
            COUNT(*) AS cnt
    FROM forgerock 
               WHERE lastname in ( select lastname from forgerock
                        where lastmodifieddate='2016-12-30')
    GROUP BY productionunit, lastname
     HAVING count(*)>1 ) b
     ON a.lastname = b.lastname and a.productionunit=b.productionunit

这个查询给了我这个结果:

| id | firstname | lastname | productionunit |           lastmodifieddate |
|----|-----------|----------|----------------|----------------------------|
|  1 |      John |    Smith |          UnitA | December, 29 2016 00:00:00 |
|  2 |    Rachel |    Smith |          UnitA | December, 31 2016 00:00:00 |
|  5 |       Mac |    Telly |          UnitB | December, 30 2016 00:00:00 |
|  6 |   Lincoln |    Telly |          UnitB |  January, 01 2017 00:00:00 |

这是想要的结果

| id | firstname | lastname | productionunit |           lastmodifieddate |
|----|-----------|----------|----------------|----------------------------|
|  5 |       Mac |    Telly |          UnitB | December, 30 2016 00:00:00 |
|  6 |   Lincoln |    Telly |          UnitB |  January, 01 2017 00:00:00 |

我认为问题出在我的 where 子句中的子查询上。我搜索了最后修改日期为 12-30 的所有姓氏，而实际上我只想要生产单元内修改日期为 12-30 的姓氏

希望这是有道理的

谢谢

这是架构的 sqlfiddle 链接:http://sqlfiddle.com/#!9/63598/1

Answer 1

您可以将子查询更改为更简单，因为您只是尝试查找在该日期编辑了生产单位/姓氏组合的任何实例。下面的子查询就可以做到这一点

  SELECT DISTINCT
    needle.productionunit,
    needle.lastname
  FROM forgerock needle
  INNER JOIN forgerock haystack
    ON haystack.lastname = needle.lastname
    AND haystack.productionunit = needle.productionunit
    AND haystack.id != needle.id
  WHERE needle.lastmodifieddate = '2016-12-30'

它在你的 SQL fiddle 中:http://sqlfiddle.com/#!9/63598/6