如何编写Select Query查询表中重复值并返回带计数的指定格式结果

Alright, based on your table structure and sample data, it looks like we need to identify duplicates where CustomerName, DocumentValue, and Date all match (since those are the fields that repeat across your target rows). Here's a SQL query that will return each duplicate record along with the total number of times that combination repeats:

SELECT 
    Docnum,
    CustomerName,
    DocumentValue,
    Date,
    COUNT(*) OVER (PARTITION BY CustomerName, DocumentValue, Date) AS Count
FROM 
    YourTableName
WHERE 
    COUNT(*) OVER (PARTITION BY CustomerName, DocumentValue, Date) > 1
ORDER BY 
    CustomerName, Date, Docnum;

How this works: #

• PARTITION BY CustomerName, DocumentValue, Date: This groups records by the three fields that define a duplicate. The COUNT(*) will calculate how many records are in each group.
• The WHERE clause filters out any groups that only have one record (so we only keep duplicates).
• The ORDER BY is optional but helps organize the results to show matching duplicates together.

Sample Output: #

Just replace YourTableName with the actual name of your table, and this should work for most modern SQL databases (like PostgreSQL, SQL Server, MySQL 8.0+, etc.).

内容的提问来源于stack exchange，提问作者Naveen.A