复杂sql语句集合

----有一次迁移线上数据，我们用游标把数据从一张表迁移到另外一张表（链接地址：https://www.cnblogs.com/ykpkris/p/15114486.html），但是太年轻了，有一次迁移差不多百万记录的数据，用游标预估时间是五个小时，后来用了一下两个稍微复杂点的sql语句

一 转移数据从一张表到另一张表

1. 复制表结构及其数据：

create table table_name_new as select * from table_name_old

2. 只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;

或者：

create table table_name_new like table_name_old

3. 只复制表数据：

如果两个表结构一样：

insert into table_name_new select * from table_name_old

如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

 二 删除重复的数据并保留一个

 举个例子：

DELETE from t_order where user_id in (
     SELECT t.user_id from (SELECT user_id from t_order HAVING count(user_id) > 1) t
) and id not in (
     SELECT t.id from (SELECT min(id) as id from t_order HAVING count(user_id) > 1) t
);

效果：

 当然如果要去掉多列重复的数据，例如去掉重复的user_id,product_id，可以这么操作：

原表：

DELETE from t_order where (user_id,product_id) in (
	 SELECT t.user_id,t.product_id from (
	    SELECT user_id, product_id from t_order
		   	GROUP BY user_id, product_id HAVING count(1) > 1) t
) and id not in (
	 SELECT t.id from (SELECT min(id) as id from t_order
		   	GROUP BY user_id, product_id HAVING count(1) > 1) t
);

 效果：

 

                                                                                                                        —————记于2021.10.23