在MySQL中并行显示所有重复项 [英] Showing all duplicates, side by side, in MySQL

问题描述

我有一张这样的表：

  Table eventlog 
 user | user_group | event_date | event_dur。 
 ---- ---------- --------- ---------- 
 xyz 1 2009-1-1 3.5 
 xyz 2 2009-1-1 4.5 
 abc 2 2009-1-2 5 
 abc 1 2009-1-2 5 
   
 
 <幸运的是，我有一个相当强烈的想法（因为这是对旧系统的更新），哪些记录是正确的。 （基本上，这一切都是为了无缝地将旧数据库与新数据库合并）。
 
 
 
不幸的是，我必须或多或少地用手做这个，或者风险丢失数据只存在于一方而不是其他方面.... 
 
 
 
长篇小说，我试图找出正确的MySQL查询返回所有在任何给定日期，用户拥有多个条目的记录。我一直在和GROUP BY和HAVING一起努力，但是我能得到的最好的是每个重复的两个重复项之一，如果我确定这是错误的，那将是很棒的。
 
 
 
这是最近我来的：
  SELECT * 
 FROM eventlog 
 GROUP BY event_date，user 
 HAVING COUNT（user）> 1 
 ORDER BY event_date，user 
  
对此的任何帮助都将非常有用。如果需要，我有每个重复的用户/日期列表，所以我可以手动删除所有400个，但我更愿意一次看到它们。
 
 
 
谢谢！
解决方案
这个工作吗？
  SELECT event_date，user 
 FROM eventlog 
 GROUP BY event_date，user 
 HAVING COUNT（*）> 1 
 ORDER BY event_date，user 
  
我抛出的是COUNT（用户）子句你有。
 
I have a table like so:
Table eventlog
user  |  user_group  | event_date |  event_dur.
----     ----------    ---------     ----------
xyz         1           2009-1-1        3.5
xyz         2           2009-1-1        4.5
abc         2           2009-1-2        5
abc         1           2009-1-2        5
Notice that in the above sample data, the only thing reliable is the date and the user. Through an over site that is 90% mine to blame, I have managed to allow users to duplicate their daily entries. In some instances the duplicates were intended to be updates to their duration, in others it was their attempt to change the user_group they were working with that day, and in other cases both.

Fortunately, I have a fairly strong idea (since this is an update to an older system) of which records are correct. (Basically, this all happened as an attempt to seamlessly merge the old DB with the new DB).

Unfortunately, I have to more or less do this by hand, or risk losing data that only exists on one side and not the other....

Long story short, I'm trying to figure out the right MySQL query to return all records that have more than one entry for a user on any given date.  I have been struggling with GROUP BY and HAVING, but the best I can get is a list of one of the two duplicates, per duplicate, which would be great if I knew for sure it was the wrong one.

Here is the closest I've come:
SELECT *
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(user) > 1
ORDER BY event_date, user
Any help with this would be extremely useful. If need be, I have the list of users/date for each set of duplicates, so I can go by hand and remove all 400 of them, but I'd much rather see them all at once.

Thanks!
解决方案
Would this work?
SELECT event_date, user
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) > 1
ORDER BY event_date, user
What's throwing me off is the COUNT(user) clause you have.

                        
这篇关于在MySQL中并行显示所有重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！