SQL-如何计算有条件的重复项

Question

我有一个如下表：

ID   Doc ID      Names
XX    97...      Name1
XY    97...      Name 2
XZ    98...      Name 1
...

现在，我想查找特定名称的所有重复项，但只有名称中的任何重复项包含以97开头的文档Id。SQL的简单重复查询没有帮助，请帮助。

预期输入&输出

Answer 1

考虑：

SELECT Table1.*, Query1.CountOfDocID
FROM (SELECT Names, Count(DocID) AS CountOfDocID FROM Table1 GROUP BY Names) AS Query1 
INNER JOIN Table1 ON Query1.Names = Table1.Names
WHERE CountOfDocID>1 AND Table1.Names IN (SELECT Names FROM Table1 WHERE DocID LIKE "97*");

在回答您的评论问题时，将Allen Browne网站上的功能放入一个通用模块中。调用查询或文本框中的函数

SELECT DISTINCT Table1.Names, Query1.[CountOfDocID], 
ConcatRelated("DocID","Table1","Names='" & [Table1]![Names] & "'") AS Docs
FROM (SELECT Names, Count(DocID) AS CountOfDocID FROM Table1 GROUP BY Names)  AS Query1 
INNER JOIN Table1 ON Query1.Names = Table1.Names
WHERE [CountOfDocID]>1 AND Table1.Names In (SELECT Names FROM Table1 WHERE DocID LIKE "97*");

Answer 2

SELECT First(tblEmployee.[empid]) AS [tblEmployee.EmployeeID], Count([tblEmployee.empid]) AS NumberOfDups
FROM tblEmployee
GROUP BY tblEmployee.[empid]
HAVING (((Count(tblEmployee.[empid]))>1));

https://www.wikihow.com/Find-Duplicates-Easily-in-Microsoft-Access