sql操作记录

Posted on |

主要记录一些sql的订正的操作。

insert操作无需指定自增id

1
2
-- insert的时候指定一个列表
insert into error_level_conf (biz_type,error_type,error_level,handle_num_limit) value("general_b2C","CREATE_COUPON_PAYMENT",1,10);

从一个表拷贝数据到另一个表并订正

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- retail_coupon.eticket_status 已经落表的数据直接已登账的状态，定金支付完成的订单状态为已分账，这里关联订单表状态150的为已登账，大于150为已分账
-- retail_marketing_order.entry_status 和retail_marketing_order.split_account_status中台不使用，订正到已登账和已分账状态

INSERT INTO retail_coupon (retail_order_code, eticket_status, tmall_order_id, coupon_type, step_no
	, disburse_fee, discount_price, delete_mark, tmall_sku_id, date_create
	, date_update, business_type, discount_stage, entry_status, split_account_status)
SELECT t1.retail_order_code
	, (
		SELECT CASE t2.order_status
				WHEN 150 THEN 3
				ELSE 4
			END
	), t1.tmail_order_id, 1, t1.step_count, t1.disburse_fee
	, t1.voucher_price, t1.deleted, t1.sku_id, t1.date_create, t1.date_update
	, 'distributor', 'retainge', 50, 50
FROM retail_marketing_order t1
	LEFT JOIN retail_order t2 ON convert(t1.retail_order_code USING utf8) = t2.order_code;

从一个表匹配数据并更新到另一个表

1
2
3
4
5
6
7
8
9
10
11
UPDATE retail_invoice_info t1
	LEFT JOIN retail_order t2 ON t1.purchase_order_id = t2.purchase_order_id
SET t1.make_type = 'B2c', t1.invoice_type = (
	SELECT CASE t2.plate_user_type
			WHEN 'individual' THEN 'personal'
			ELSE 'enterprise'
		END
)
WHERE (t1.order_code LIKE '90%'
	AND t1.invoice_type = ''
	AND t2.plate_user_type IS NOT NULL);