Microsoft Access或SQL Server中的重复查询

I have over 1,000 entries with duplicate values in an Access database. I want to create a column called Duplicate and set it to true if a record is NOT THE FIRST ONE with a particular value. This means, if the record is the first one with the value "Red Chair", the Duplicate field is set to false, but all subsequent records with value "Red Chair" will have the Duplicate field set to true.

我在Access数据库中有超过1,000个具有重复值的条目。我想创建一个名为Duplicate的列，如果记录不是具有特定值的第一个记录，则将其设置为true。这意味着，如果记录是第一个值为“Red Chair”的记录，则Duplicate字段将设置为false，但所有后续值为“Red Chair”的记录都将Duplicate字段设置为true。

How do I perform this query in Access?

如何在Access中执行此查询？

This database will be upsized to an SQL Server database, so an option for me is to 'ignore' the duplicate records while retrieving the records in my SQL query. If this option is viable, I'd like to know how to do this in SQL as alternative. Thanks.

此数据库将升迁到SQL Server数据库，因此我选择在检索SQL查询中的记录时“忽略”重复记录。如果这个选项可行，我想知道如何在SQL中做这个替代方案。谢谢。

3 个解决方案

#1

You will have to use subqueries. Try this

您将不得不使用子查询。尝试这个

UPDATE Tabelle1 SET Tabelle1.b = 'Duplicate'
WHERE 
((Tabelle1.[ID] In 
 (SELECT Tabelle1.[ID] FROM Tabelle1 WHERE 
  ((Tabelle1.[a] In 
     (SELECT [a] FROM [Tabelle1] As Tmp GROUP BY [a] HAVING Count(*)>1 )
   )
  AND 
  (Tabelle1.[ID] Not In 
    (SELECT min([id]) FROM [Tabelle1] as grpid GROUP BY [a] HAVING Count(*)>1)
  ));
)));

#2

I'm no expert on the Access dialect, but this adaptation of RJIGO's answer or something similar may also work and be more efficient:

我不是Access方言的专家，但是这种对RJIGO答案或类似方法的改编也可以起作用并且效率更高：

UPDATE Tabelle1 SET
  b = 'Duplicate'
WHERE 
  Tabelle1.[ID] > (
    SELECT min([id])
    FROM [Tabelle1] as T2
    WHERE T2.[a] = Tabelle1.[a]
  );

#3

I hope this sql help u:

我希望这个sql帮助你：

SELECT table.field, Count(table.field) AS test, IIf([test]>1,"TRUE","FALSE") AS check FROM table GROUP BY table.field, IIf([test]>1,"TRUE","FALSE");

SELECT table.field，Count（table.field）AS test，IIf（[test]> 1，“TRUE”，“FALSE”）AS check FROM table GROUP BY table.field，IIf（[test]> 1，“TRUE” “，“假”）;

#1