MYSQL选择用户名和日期对多次出现的所有记录 [英] MYSQL select all records where username and date pair occur more than once

问题描述

我想获得一组记录，其中多个记录中只有日期(YYYY-MM-DD)和用户名对匹配，其他所有字段都可以不同.

I want to get a set of records where only the date (YYYY-MM-DD) and the username pair match in multiple records, all the other fields can be different.

SELECT *, count(*) from table 
GROUP BY username 
HAVING count(username)>1 AND count(date)>1;

似乎是严重错误，我不确定如何使它起作用. 我在这里看到的大多数帮助都是关于匹配的整个行，我只关心这两列上匹配的记录.我要标记这些重复项.

Appears to be severely wrong, and I'm not sure how to make it work. Most of the help I see here is about entire rows that match, I am only concerned about records that match on these two columns. I want to flag these duplicates.

我无法控制该数据库，因此无法接受将来的维护建议.

I have no control over this database, so I can't take future maintenance suggestions.

推荐答案

create table table1
(
    id int auto_increment primary key,
    username varchar(30) not null,
    `date` date not null
);

insert table1 (username,`date`) values ('john','2015-01-01');
insert table1 (username,`date`) values ('kim','2015-01-01');
insert table1 (username,`date`) values ('john','2015-01-01');
insert table1 (username,`date`) values ('john','2015-02-01');
insert table1 (username,`date`) values ('john','2015-03-01');

SELECT t1.* 
from table1 t1
join
(
    select username,`date`,count(*)
    from table1
    group by username,`date`
    having count(username)>1
) inr
on inr.username=t1.username and inr.`date`=t1.`date`

结果显示为2行

+----+----------+------------+
| id | username | date       |
+----+----------+------------+
|  1 | john     | 2015-01-01 |
|  3 | john     | 2015-01-01 |
+----+----------+------------+
2 rows in set (0.03 sec)

根据OP请求，与select语句相反，有一列来标记重复对象以供以后工作.请注意，您可以Alter Table并添加此可为空的标志列，进行设置，在闲暇时使用值，稍后在Alter Table并将其放下.

as per OP request, have a column to flag dupes for later work, as opposed to a select statement. Note you can Alter Table and add this nullable flag column, set it, use values at your leisure, later Alter Table and drop it.

但是我将从带有新标志列的创建表开始:

But I will just start over here with the create table with new flag column:

create table table1
(
    id int auto_increment primary key,
    username varchar(30) not null,
    `date` date not null,
    dupeflag int null --    <---- New flag column, nullable, ignored on inserts below
);

insert table1 (username,`date`) values ('john','2015-01-01');
insert table1 (username,`date`) values ('kim','2015-01-01');
insert table1 (username,`date`) values ('john','2015-01-01');
insert table1 (username,`date`) values ('john','2015-02-01');
insert table1 (username,`date`) values ('john','2015-03-01');

update table1 t1
join 
(   select username,`date`,count(*)
    from table1
    group by username,`date`
    having count(username)>1
) inr
on inr.username=t1.username and inr.`date`=t1.`date`
set dupeflag=1;

-- 2 rows affected

select * from table1;

+----+----------+------------+----------+
| id | username | date       | dupeflag |
+----+----------+------------+----------+
|  1 | john     | 2015-01-01 |        1 |
|  2 | kim      | 2015-01-01 |     NULL |
|  3 | john     | 2015-01-01 |        1 |
|  4 | john     | 2015-02-01 |     NULL |
|  5 | john     | 2015-03-01 |     NULL |
+----+----------+------------+----------+
5 rows in set (0.00 sec)

这篇关于MYSQL选择用户名和日期对多次出现的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！