数据库 结转数据 sql结转

//查询旧表一天的数据
SELECT COUNT(*) FROM orders
WHERE created_time >= 1561824000 and created_time <1561910400;

//查询结转表这一天是否有数据
SELECT COUNT(*) FROM zb_orders
WHERE created_time >= 1561824000 and created_time <1561910400;

//查询结转表是否有旧表的数据
SELECT COUNT(*) FROM orders where order_id not in (SELECT order_id FROM zb_orders
WHERE created_time >= 1561824000 and created_time < 1561910400)
AND created_time >= 1561824000 and created_time < 1561910400;

//如果有旧表的数据那么只插入旧表不存在的数据
INSERT INTO zb_orders
SELECT * from orders where id not in (
SELECT id from zb_orders where created_time >= 1561824000 and created_time < 1561910400
) AND created_time >= 1561824000 and created_time < 1561910400;

//如果不存在旧表数据那么执行这个
INSERT INTO zb_orders
SELECT * FROM orders
WHERE created_time >= 1561824000 and created_time <1561910400;

//最后删除旧表的数据
DELETE FROM orders WHERE created_time >= 1561824000 and created_time <1561910400;