tb_id user_id be_user_id
1 1 2
2 1 2
3 1 3
4 1 3
5 1 4
6 1 4
7 1 6
8 1 6
9 2 3
10 2 4
11 2 4
12 2 5
这是我的原始数据,先我要删除be_user_id 的重复记录
我要的结果
tb_id user_id be_user_id
1 1 2
3 1 3
5 1 4
7 1 6
9 2 3
10 2 4
12 2 5
这样没重复的记录我写的SQL :
1:create temporary table tmp_wrap select * from baby_user_good_friend_msg group by be_user_id having count(1) >= 1;
truncate table baby_user_good_friend_msg;
insert into baby_user_good_friend_msg select * from tmp_wrap;2:
delete baby_user_good_friend_msg as a from baby_user_good_friend_msg as a,(select *,min(tb_id) from baby_user_good_friend_msg group by user_id having count(1) > 1) as b where a.user_id = b.user_id and a.tb_id > b.tb_id ;
这2种方案都不行???
1 1 2
2 1 2
3 1 3
4 1 3
5 1 4
6 1 4
7 1 6
8 1 6
9 2 3
10 2 4
11 2 4
12 2 5
这是我的原始数据,先我要删除be_user_id 的重复记录
我要的结果
tb_id user_id be_user_id
1 1 2
3 1 3
5 1 4
7 1 6
9 2 3
10 2 4
12 2 5
这样没重复的记录我写的SQL :
1:create temporary table tmp_wrap select * from baby_user_good_friend_msg group by be_user_id having count(1) >= 1;
truncate table baby_user_good_friend_msg;
insert into baby_user_good_friend_msg select * from tmp_wrap;2:
delete baby_user_good_friend_msg as a from baby_user_good_friend_msg as a,(select *,min(tb_id) from baby_user_good_friend_msg group by user_id having count(1) > 1) as b where a.user_id = b.user_id and a.tb_id > b.tb_id ;
这2种方案都不行???
SELECT t3.tb_id
FROM (SELECT tb_id
FROM test2
GROUP BY user_id, be_user_id
)t3
)
where tb_id in (
select b.tb_id from (
select max(tb_id) as tb_id,user_id,be_user_id
from temp1
group by user_id,be_user_id
having count(user_id) > 1) b)
一,复制老表并排除重复create table table_new as select * from table_old group by be_user_id
二,删除老表三,重命名新表