sql示例

-- 收支明细（订单类型）
update tb_cloud_income_expenditure a LEFT JOIN tb_cloud_order_app b on a.order_id = b.order_id
set a.order_type = b.order_type
WHERE a.order_type is null;

-- 收支明细（应用名称）
update tb_cloud_income_expenditure a LEFT JOIN tb_cloud_order_app b on a.order_id = b.order_id
set a.app_name = b.product_app_name
WHERE a.app_name is null;

-- 收支明细（收支对象 开通）
update tb_cloud_income_expenditure a LEFT JOIN tb_cloud_order_app b on a.order_id = b.order_id
set a.obj_name = if(LENGTH(b.buyer_name)>0, b.buyer_name, b.buyer_username)
WHERE a.settle_type = 0 and a.obj_name is null;

-- 收支明细（收支对象 开发者结算）
update tb_cloud_income_expenditure a LEFT JOIN tb_cloud_order_settle b on a.order_id = b.order_id
LEFT JOIN sys_user c on b.user_id = c.id
LEFT JOIN tb_company_authentication_info d on c.authentication_sn = d.authentication_sn
set a.obj_name = d.company_name
WHERE a.settle_type = 10 and a.obj_name is null;

-- 收支明细（收支对象 渠道商结算）
update tb_cloud_income_expenditure a LEFT JOIN tb_cloud_order_settle b on a.order_id = b.order_id
LEFT JOIN tb_cloud_channel c on b.user_id = c.user_id
set a.obj_name = c.`name`
WHERE a.settle_type = 20 and a.obj_name is null;

-- 收支明细（收支对象 手续费）
update tb_cloud_income_expenditure a LEFT JOIN tb_cloud_order_settle b on a.order_id = b.order_id
LEFT JOIN tb_cloud_channel c on b.user_id = c.user_id
set a.obj_name = '易宝'
WHERE a.settle_type in (30,40) and a.obj_name is null;

-- 新增字段后初始化
update tb_user_login_record a left JOIN sys_user b on a.user_sn = b.usersn set a.phone = b.phone;

-- 初始化中台关系表过期时间
UPDATE tb_cloud_relation_centerstage a LEFT JOIN ( SELECT max(end_time) end_time, relation_id, app_type
FROM tb_cloud_charge_reation WHERE app_type = 10 GROUP BY relation_id ) b ON a.id = b.relation_id SET a.due_time = b.endTime
WHERE b.app_type = 10

-- 初始化应用关系表过期时间
UPDATE tb_app_relations AS a LEFT JOIN ( SELECT MAX(end_time) AS end_time, relation_id FROM tb_cloud_charge_reation
WHERE app_type = 0 GROUP BY relation_id ) AS b ON a.id = b.relation_id SET a.due_time = b.end_time

---

-- 在查询中更新
UPDATE `fwq_event_record` AS t1 JOIN( SELECT MAX( id) AS id FROM `fwq_event_record` WHERE `status` = 0 GROUP BY sn HAVING sn IN ( '川R181633' ) ) AS t2 ON t1.id = t2.id SET t1.STATUS = 0;

-- 在查询中删除
DELETE FROM fwq_image WHERE id IN( SELECT id FROM ( SELECT id FROM fwq_image WHERE TIMESTAMPDIFF( DAY, create_time, NOW()) > 30) AS sub )

-- 在查询中更新
UPDATE `fwq_event_record` SET `status` = 0 WHERE `id` IN( SELECT * FROM ( SELECT MAX( id) AS id FROM `fwq_event_record` WHERE `status` = 0 GROUP BY sn HAVING sn IN ( '川R181633' ) ) AS sub )