74 行
2.5 KiB
SQL
74 行
2.5 KiB
SQL
-- 清理分销+区域代理联合测试用户（910001~910009）及相关数据
|
||
-- 执行前请备份数据库
|
||
-- 用法：在 MySQL 客户端执行本文件
|
||
|
||
SET @ts = UNIX_TIMESTAMP();
|
||
|
||
-- ========== 1. 收集关联订单 ==========
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_ra_test_orders;
|
||
CREATE TEMPORARY TABLE tmp_ra_test_orders (order_id INT PRIMARY KEY);
|
||
|
||
INSERT INTO tmp_ra_test_orders (order_id)
|
||
SELECT DISTINCT id FROM `fa_shopro_order`
|
||
WHERE user_id BETWEEN 910001 AND 910009
|
||
OR id IN (910001001, 910001002);
|
||
|
||
INSERT IGNORE INTO tmp_ra_test_orders (order_id)
|
||
SELECT DISTINCT order_id FROM `fa_shopro_regional_agent_order`
|
||
WHERE buyer_id BETWEEN 910001 AND 910009
|
||
OR agent_user_id BETWEEN 910001 AND 910009
|
||
OR order_id IN (910001001, 910001002);
|
||
|
||
-- ========== 2. 区域代理佣金/订单 ==========
|
||
DELETE FROM `fa_shopro_regional_agent_reward`
|
||
WHERE agent_user_id BETWEEN 910001 AND 910009
|
||
OR buyer_id BETWEEN 910001 AND 910009
|
||
OR order_id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
DELETE FROM `fa_shopro_regional_agent_order`
|
||
WHERE buyer_id BETWEEN 910001 AND 910009
|
||
OR agent_user_id BETWEEN 910001 AND 910009
|
||
OR order_id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
DELETE FROM `fa_shopro_regional_agent`
|
||
WHERE user_id BETWEEN 910001 AND 910009;
|
||
|
||
-- ========== 3. 分销佣金/订单 ==========
|
||
DELETE FROM `fa_shopro_commission_reward`
|
||
WHERE agent_id BETWEEN 910001 AND 910009
|
||
OR buyer_id BETWEEN 910001 AND 910009
|
||
OR order_id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
DELETE FROM `fa_shopro_commission_order`
|
||
WHERE buyer_id BETWEEN 910001 AND 910009
|
||
OR agent_id BETWEEN 910001 AND 910009
|
||
OR order_id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
DELETE FROM `fa_shopro_commission_agent`
|
||
WHERE user_id BETWEEN 910001 AND 910009;
|
||
|
||
-- ========== 4. 商城订单 ==========
|
||
DELETE FROM `fa_shopro_order_item`
|
||
WHERE order_id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
DELETE FROM `fa_shopro_order_invoice`
|
||
WHERE order_id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
DELETE FROM `fa_shopro_order`
|
||
WHERE id IN (SELECT order_id FROM tmp_ra_test_orders);
|
||
|
||
-- ========== 5. 用户附属数据 ==========
|
||
DELETE FROM `fa_shopro_user_wallet_log`
|
||
WHERE user_id BETWEEN 910001 AND 910009;
|
||
|
||
DELETE FROM `fa_shopro_user_wallet_apply`
|
||
WHERE user_id BETWEEN 910001 AND 910009;
|
||
|
||
DELETE FROM `fa_shopro_user_address`
|
||
WHERE user_id BETWEEN 910001 AND 910009;
|
||
|
||
DELETE FROM `fa_user`
|
||
WHERE id BETWEEN 910001 AND 910009;
|
||
|
||
SELECT '分销+区域代理测试数据已清理（910001~910009）' AS info;
|