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，名称，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将起作用. (即不是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屋！