create table uses{
id int(11) auto_increment,
useid varchar(30) not null,
server varhcar(10) not null,
date datetime not null
}Alter table user add unique(useid,server);insert into uses values(0,'wowo',1,'2011-01-12 00:00:00');
insert into uses values(0,'wowo',2,'2011-01-15 00:00:00');
insert into uses values(0,'aa','2011-01-15 00:00:00');
insert into uses values(0,'aa','2011-01-12 00:00:00');我想把2各区的数据合并,但是2区和1区数据可能相同,所以触犯唯一约束,之后还要筛选2各合并后只留下时间比较早的那个帐号,另外一个删除
id int(11) auto_increment,
useid varchar(30) not null,
server varhcar(10) not null,
date datetime not null
}Alter table user add unique(useid,server);insert into uses values(0,'wowo',1,'2011-01-12 00:00:00');
insert into uses values(0,'wowo',2,'2011-01-15 00:00:00');
insert into uses values(0,'aa','2011-01-15 00:00:00');
insert into uses values(0,'aa','2011-01-12 00:00:00');我想把2各区的数据合并,但是2区和1区数据可能相同,所以触犯唯一约束,之后还要筛选2各合并后只留下时间比较早的那个帐号,另外一个删除
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
server代表区的意思,useid代表用户id
create table uses{
id int(11) auto_increment,
useid varchar(30) not null,
server varhcar(10) not null,
date datetime not null
}Alter table user add unique(useid,server);insert into uses values(0,'wowo',1,'2011-01-12 00:00:00');
insert into uses values(0,'wowo',2,'2011-01-15 00:00:00');
insert into uses values(0,'aa',1,'2011-01-15 00:00:00');
insert into uses values(0,'aa',2,'2011-01-12 00:00:00');
我想把2区的帐号的区号全部改为1,但是会触犯唯一约束。
帐号相同的话就留下
date比较靠前的那一个,帐号不同就直接将区号改为1
`id` INT(11) NOT NULL AUTO_INCREMENT,
`server` VARCHAR(10) NOT NULL,
`date` DATETIME NOT NULL,
`useid` VARCHAR(30) NOT NULL,KEY(`id`) );
ALTER TABLE `uses` ADD UNIQUE(useid,`server`);INSERT INTO uses VALUES(0,'wowo',1,'2011-01-12 00:00:00');
INSERT INTO uses VALUES(0,'wowo',2,'2011-01-15 00:00:00');
INSERT INTO uses VALUES(0,'aa',1,'2011-01-15 00:00:00');
INSERT INTO uses VALUES(0,'aa',2,'2011-01-12 00:00:00');要求结果是什么
DELETE a FROM ua a INNER JOIN ua b ON a.`server`=b.`server` AND a.`date`>b.`date`;
INSERT INTO uses VALUES(0,'show',1,'2011-01-12 00:00:00');
INSERT INTO uses VALUES(0,'wuwu',2,'2011-01-15 00:00:00');
INSERT INTO uses VALUES(0,'aa',1,'2011-01-15 00:00:00');
INSERT INTO uses VALUES(0,'aa',2,'2011-01-12 00:00:00');
INSERT INTO uses VALUES(0,'bb',1,'2011-01-12 00:00:00');
INSERT INTO uses VALUES(0,'bb',2,'2011-01-15 00:00:00');
得到的结果id useid server date
1 show 1 2011-01-12 00:00:00
2 wuwu 1 2011-01-15 00:00:00
3 aa 1 2011-01-12 00:00:00
4 bb 1 '2011-01-12 00:00:00
WHERE NOT EXISTS(SELECT 1 FROM uses WHERE a.`useid`=`useid` AND a.`date`>`date`);
insert ignore into uses values(0,'wowo',2,'2011-01-15 00:00:00');
insert ignore into uses values(0,'aa','2011-01-15 00:00:00');
insert ignore into uses values(0,'aa','2011-01-12 00:00:00');
加ignnore
加入现在表中数据是这样的
id useid server date
1 show 1 2011-01-12 00:00:00
2 show 2 2011-01-15 00:00:00
3 aa 1 2011-01-12 00:00:00
4 bb 2 2011-01-12 00:00:00
5 wuwu 1 2011-01-12 00:00:00
6 wuwu 2 2011-01-11 00:00:00
我要得到的结果是
id useid server date
1 show 1 2011-01-12 00:00:00
3 aa 1 2011-01-12 00:00:00
4 bb 1 2011-01-12 00:00:00
6 wuwu 2 2011-01-11 00:00:00就是说最终的结果是要把server都改为1,但是改为1的时候 有些useid 相同,就触犯了唯一约束,这个时候就要删除date比较晚的那一条。
+----+--------+--------+---------------------+
| id | userid | server | date |
+----+--------+--------+---------------------+
| 1 | show | 1 | 2011-01-12 00:00:00 |
| 3 | aa | 1 | 2011-01-12 00:00:00 |
| 4 | bb | 1 | 2011-01-12 00:00:00 |
| 6 | wuwu | 1 | 2011-01-11 00:00:00 |
+----+--------+--------+---------------------+
4 rows in set (0.00 sec)
mysql> create temporary table xx as select * from user;
Query OK, 6 rows affected (0.31 sec)
Records: 6 Duplicates: 0 Warnings: 0将user 表中的数据插入临时表,这时需要判断server ,如果是2需要改为1并且按日期拍序后面按useid聚合mysql> insert into xx select t.id,t.useid,(case when t.server<>1 then 1 else t.s
erver end) as server,t.date from (select * from user order by date) as t group b
y useid order by id;删除原表数据
mysql> delete from user;
Query OK, 6 rows affected (0.05 sec)将临时表数据插入mysql> insert into user select * from xx;
Query OK, 4 rows affected (0.12 sec)
Records: 4 Duplicates: 0 Warnings: 0验证是否成功mysql> insert into user select * from xx;
Query OK, 4 rows affected (0.12 sec)
Records: 4 Duplicates: 0 Warnings: 0最后删除临时表。
验证是否成功贴错了mysql> select * from user;
+----+-------+--------+---------------------+
| id | useid | server | date |
+----+-------+--------+---------------------+
| 1 | show | 1 | 2011-01-12 00:00:00 |
| 2 | wuwu | 1 | 2011-01-15 00:00:00 |
| 4 | aa | 1 | 2011-01-12 00:00:00 |
| 5 | bb | 1 | 2011-01-12 00:00:00 |
+----+-------+--------+---------------------+
4 rows in set (0.00 sec)
create table
users
as
(select t.id,t.userid,1 server,t.date from
(select id,userid,min(date) date group by userid) as t
order by id);