非重复记录的 SQL 查询 [英] SQL query for non duplicate records

问题描述

我正在尝试构建一个查询，该查询将返回表中的所有非重复(唯一)记录.查询将需要使用多个字段来确定记录是否重复.

I'm attempting to build a query that will return all non duplicate (unique) records in a table. The query will need to use multiple fields to determine if the records are duplicate.

例如，如果一个表有以下字段；PKID、ClientID、Name、AcctNo、OrderDate、Charge，我想使用 AcctNo、OrderDate 和 Charge 字段来查找唯一记录.

For example, if a table has the following fields; PKID, ClientID, Name, AcctNo, OrderDate, Charge, I'd like to use the AcctNo, OrderDate and Charge fields to find unique records.

表格

PKID-----ClientID-----Name-----AcctNo-----OrderDate-----Charge
1        JX100        John     12345      9/9/2010      $100.00
2        JX220        Mark     55567      9/9/2010       $23.00
3        JX690        Matt     89899      9/9/2010      $218.00
4        JX100        John     12345      9/9/2010      $100.00

查询的结果需要是:

PKID-----ClientID-----Name-----AcctNo-----OrderDate-----Charge
2        JX220        Mark     55567      9/9/2010       $23.00
3        JX690        Matt     89899      9/9/2010      $218.00

我尝试过使用 SELECT DISTINCT，但这不起作用，因为它会在结果中保留一条重复记录.我也尝试过使用 HAVING COUNT = 1，但这会返回所有记录.

I've tried using SELECT DISTINCT, but that doesn't work because it keeps one of the duplicate records in the result. I've also tried using HAVING COUNT = 1, but that returns all records.

感谢您的帮助.

推荐答案

HAVING COUNT(*) = 1 如果只包含 GROUP BY 中的字段，则将起作用您用来查找唯一记录的.(即不是 PKID，但您可以使用 MAX 或 MIN 来返回它，因为结果集中每组只有一条记录.)

HAVING COUNT(*) = 1 will work if you only include the fields in the GROUP BY that you're using to find the unique records. (i.e. not PKID, but you can use MAX or MIN to return that since you'll only have one record per group in the results set.)

这篇关于非重复记录的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！