Mysql 在每个用户名第一次出现时更新列 [英] Mysql update column on first occurrence of each username
问题描述
我正在尝试标记每个用户名每天第一次出现.我已经将所有相关人员标记为 dupe = 1.那天的第一个应该设置为 dupe = 2.
I'm trying to mark the first occurrence per day of each username. I already have all concerning people marked as dupe = 1. The first for that day should get set to dupe = 2.
用外行的话来说,如果今天第一次输入这个用户名,标记列 = 2.每天都做.
Like in layman's terms, If first entry of this username for this day, mark column = 2. Do for each day.
基于这个问题https://stackoverflow.com/a/12065956/1811162我可以做到这一点>
Based on this question https://stackoverflow.com/a/12065956/1811162 I can make this
Select *
from (
select * from table WHERE dupe=1 order by date desc
) x
group by date
它返回我正在查找的每个重复项的一个成员,但我想将其设置为 2.我无法将其设为更新语句.或者这甚至可以作为更新语句?我只想设置第一个成员.
Which returns one member of each duplicate I'm looking for, but I'd like to set that one = 2. I am having trouble making this an update statement. Or would this even work as an update statement? I only want the first member to set.
我想要的结果是 -
Select username, dupe where dupe!= 0;
Day 1
Bob - 2
Kathy - 2
Bob - 1
Kathy - 1
Kathy - 1
Day 2
Kathy - 2
Kathy - 1
Bob - 2
Kathy - 1
我尝试的是
UPDATE table set dupeflag=2 from (
select * from
from (
select * from table WHERE dupeflag=1 order by date desc
) x
group by date
)
但没有运气.可能大错特错
but no luck. probably very wrong
推荐答案
我为此使用了一个新的标志列,另外还帮助您解决了其他问题 此处.
I went with a new flag column for this, plus had the benefit of helping with your other question here.
演示架构设置
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
firstFlag int null -- <-- was first dupe for day? 2=yes, 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');
insert table1 (username,`date`) values ('john','2015-03-01');
insert table1 (username,`date`) values ('kim','2015-01-01');
insert table1 (username,`date`) values ('kim','2015-02-01');
更新语句,根据PK id设置dupes和first
update statement, set dupes and first for day based on PK id
update table1 t1
join
( select username,`date`,count(*) as theCount,min(id) as minForGroup
from table1
group by username,`date`
having theCount>1
) inr
on inr.username=t1.username and inr.`date`=t1.`date`
set dupeFlag=1,
firstFlag=if(id=inr.minForGroup,2,666);
select * from table1;
+----+----------+------------+----------+-----------+
| id | username | date | dupeFlag | firstFlag |
+----+----------+------------+----------+-----------+
| 1 | john | 2015-01-01 | 1 | 2 |
| 2 | kim | 2015-01-01 | 1 | 2 |
| 3 | john | 2015-01-01 | 1 | 666 |
| 4 | john | 2015-02-01 | NULL | NULL |
| 5 | john | 2015-03-01 | 1 | 2 |
| 6 | john | 2015-03-01 | 1 | 666 |
| 7 | kim | 2015-01-01 | 1 | 666 |
| 8 | kim | 2015-02-01 | NULL | NULL |
+----+----------+------------+----------+-----------+
8 rows in set (0.00 sec)
这篇关于Mysql 在每个用户名第一次出现时更新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!