mysql - 需要根据ID去除重复项

Question

我有一个查询，它成功分析了我的表中与 100% 相关字段匹配的重复项，并返回重复项的计数。现在我需要编写一个返回记录 ID 值的查询，以便我可以删除它们。这是我最初的查询:

SELECT CompanyID, COUNT(*) AS dupecount
FROM artist_reports
GROUP BY contemporary, traditional, country, folk, functional, decorative, abstract, representational, figurative, price_range_low,
  price_range_high, best_selling_range_low, best_selling_range_high, average_sales_other_shows, total_sales_this_event, average_exhibitor_quality, financial_fairness, patrons_art_savvy, demographics, buying_energy,
  advertising, venue_environment, show_layout, organization, director_support, staff_support, logistical_ease, load_in_out, parking_ease, artist_amenities,
  awards_judging, security_efficiency, weather, event_year, critique, artist_reports.status, public_email, artist_reports.email, would_you_return, fairs_per_year, CompanyID
HAVING COUNT(*) > 1

理论上，我有一个指向另一个页面的链接，该页面传递 CompanyID，然后该页面将删除重复项。但是，我尝试了这个，它只返回一条记录:

SELECT arid FROM artist_reports WHERE arid IN (
  SELECT * FROM (
    SELECT arid
      FROM artist_reports
      WHERE CompanyID = 12345
      GROUP BY contemporary, traditional, country, folk, functional, decorative, abstract, representational, figurative, price_range_low,
        price_range_high, best_selling_range_low, best_selling_range_high, average_sales_other_shows, total_sales_this_event, average_exhibitor_quality, financial_fairness, patrons_art_savvy, demographics, buying_energy,
        advertising, venue_environment, show_layout, organization, director_support, staff_support, logistical_ease, load_in_out, parking_ease, artist_amenities,
        awards_judging, security_efficiency, weather, event_year, critique, artist_reports.status, public_email, artist_reports.email, would_you_return, fairs_per_year, CompanyID
      HAVING COUNT(*) > 1) AS a )

我不确定我做错了什么，但理想情况下我想获取报告的唯一 ID 值的记录集(干旱)，然后迭代这些记录以删除最新的，保持原来的完好无损。

Answer 1

在 oracle 中你可以使用窗口函数，但这里你需要作一点欺骗。

我假设您可能想通过 arid 删除重复项。要使用您的查询获取所有 ID，请尝试向其中添加 GROUP_CONCAT:

SELECT CompanyID, COUNT(*) AS dupecount,   
      GROUP_CONCAT(arid) AS all_duplicates_ids
FROM artist_reports
GROUP BY contemporary, traditional, country, folk, functional, decorative, abstract, representational, figurative, price_range_low,
  price_range_high, best_selling_range_low, best_selling_range_high, average_sales_other_shows, total_sales_this_event, average_exhibitor_quality, financial_fairness, patrons_art_savvy, demographics, buying_energy,
  advertising, venue_environment, show_layout, organization, director_support, staff_support, logistical_ease, load_in_out, parking_ease, artist_amenities,
  awards_judging, security_efficiency, weather, event_year, critique, artist_reports.status, public_email, artist_reports.email, would_you_return, fairs_per_year, CompanyID
HAVING COUNT(*) > 1

现在您将得到类似的结果(假设有 5 个重复项):

CompanyID|dupecount|all_duplicates_ids
---------------------------------------
12345    |      5  | '2,5,8,9, 12'

您不想删除所有 5 条记录，只需删除其中 4 条记录，因此您可以通过将 GROUP_CONCAT 部分替换为以下内容来修剪此字符串:

SUBSTRING_INDEX(
    CONCAT(
      GROUP_CONCAT(arid),
      ','),
    ',', 1) AS all_duplicates_without_one

现在你有了带有 id 的逗号分隔字符串。您可以在 where 子句中的删除查询中使用它 - 我建议使用 FIND_IN_SET 函数来检查 arid 是否在逗号分隔的字符串中:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set