个人mysql处理笔记

离叶&

已于 2023-12-19 14:49:49 修改

阅读量364

点赞数 9

文章标签： android mysql

于 2023-12-18 13:53:56 首次发布

本文链接：https://blog.csdn.net/gpx2001/article/details/135060951

版权

本文介绍了在个人MySQL数据库中进行商品SKU更新策略，包括使用UPDATE语句处理商品代码唯一性问题，以及如何在发货单表中存储和更新JSON数据结构。还涉及订单地址解析和客户聊天超时回复计数的SQL查询技巧。

摘要由CSDN通过智能技术生成

个人mysql处理笔记
五粮液数据迁移-商品sku需要设置唯一，取的是商品code不唯一，故对其实现更新

UPDATE middle_it_item_sku sku,
(
	SELECT
	IF
		( @j = item_id, @i := @i + 1, @i := 1 ),
	IF
		( @j != item_id, @j := item_id, "" ),
		@i AS sort,
		@j,
		id,
		item_id,
	CODE 
	FROM
		tmp_zhongjian.middle_it_item_sku sk,(
		SELECT
			@i := 0,
			@j := "" 
		) AS j 
	) tmp 
	SET sku.CODE = concat( tmp.CODE, '_', tmp.sort ) 
WHERE
	sku.id = tmp.id 
	AND sku.create_person = 'system'

2,发货单需要存取发货单商品的json数据

UPDATE middle_in_delivery d,
(
	SELECT
		a.delivery_id,
		JSON_ARRAYAGG(
			JSON_OBJECT(
				'id',
				a.id,
				'num',
				a.num,
				'skuId',
				a.sku_id,
				'batchId',
				a.batch_id,
				'cargoId',
				a.cargo_id,
				'imgPath',
				a.img_path,
				'skuCode',
				a.sku_code,
				'skuDesc',
				a.sku_desc,
				'itemCode',
				a.item_code,
				'itemName',
				a.item_name,
				'cargoCode',
				a.cargo_code,
				'itemPrice',
				a.item_price,
				'create_time',
				a.create_time,
				'deliveryId',
				a.delivery_id,
				'updateTime',
				a.update_time,
				'retailPrice',
				a.retail_price,
				'createPerson',
				a.create_person,
				'updatePerson',
				a.update_person 
			) 
		) AS deliveryItems 
	FROM
		tmp_zhongjian.middle_in_delivery c
		JOIN tmp_zhongjian.middle_in_delivery_item a ON a.delivery_id = c.id 
	GROUP BY
		a.delivery_id 
	) AS item 
	SET d.shipping_info = CONCAT(
		'[',
		JSON_OBJECT(
			'expressNo',
			d.extension,
			'shippingCode',(
			IF
				(
					d.carrier_name = "四川京邦达物流科技有限公司",
					"JD",
					(
					IF
					( d.carrier_name = "通用承运商", "OT", "SF" )))),
			'shippingName',
			d.carrier_name,
			'extension',
			d.extension,
			'deliveryItems',
			item.deliveryItems 
		),
		']' 
	) 
WHERE
	d.id = item.delivery_id

3，订单地址处理省市区

SELECT
	ad.*,
IF
	(
		locate( '区', SUBSTRING_INDEX( receiveAddress, '市',- 1 ) ) > 0,
		SUBSTRING_INDEX( receiveAddress, '区',- 1 ),
	IF
		(
			locate( '县', SUBSTRING_INDEX( receiveAddress, '市',- 1 ) ) > 0,
			SUBSTRING_INDEX( receiveAddress, '县',- 1 ),
			SUBSTRING_INDEX( receiveAddress, '镇',- 1 ) 
		) 
	) AS detailed,
IF
	(
		locate(
			'区',
		SUBSTRING_INDEX( receiveAddress, '市',- 1 )) > 0,
		CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市',- 1 ), '区', 1 ), '区' ),
	IF
		(
			locate(
				'县',
			SUBSTRING_INDEX( receiveAddress, '市',- 1 ))> 0,
			CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市',- 1 ), '县', 1 ), '县' ),
			CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市',- 1 ), '镇', 1 ), '镇' ) 
		) 
	) AS area_name,
IF
	(
		locate( '省', SUBSTRING_INDEX( receiveAddress, '市', 1 ) ) > 0,
		CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市', 1 ), '省',- 1 ), '市' ),
	IF
		(
			locate( '区', SUBSTRING_INDEX( receiveAddress, '市', 1 ) ) > 0,
			CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市', 1 ), '区',- 1 ), '市' ),
			CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市', 1 ), '湾',- 1 ), '市' ) 
		) 
	) AS city_name,
IF
	(
		locate( '省', SUBSTRING_INDEX( receiveAddress, '市', 1 ) ) > 0,
		CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市', 1 ), '省', 1 ), '省' ),
	IF
		(
			locate( '区', SUBSTRING_INDEX( receiveAddress, '市', 1 ) ) > 0,
			CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市', 1 ), '区', 1 ), '区' ),
			CONCAT( SUBSTRING_INDEX( SUBSTRING_INDEX( receiveAddress, '市', 1 ), '湾', 1 ), '湾' ) 
		) 
	) AS privence_name 
FROM
	temp_mallorder_1 ad

4，客户聊天计算超时回复数，超时未回复数

SELECT
	* 
FROM
	(
	SELECT
		@i AS last_type,
		@j AS last_time,
		if( to_type = 0,if(@m is null ,@m:=msgtime,@m:=@m),null) ,
		if(@i = 0 and to_type=1,@m:=@m,null ) ,
		if(@i = 1 and to_type=1,@m:=null,null ) ,
		@m as new_over_time,
		@i := to_type,
		@j := msgtime,
		mwma.* 
	FROM
		(
		SELECT
			* 
		FROM
			me_wecom_msg_audit 
		WHERE
			1 = 1 
			AND ( ( msgfrom = 'liye' AND FIND_IN_SET( 'xuanji', tolist ) ) OR ( msgfrom = 'xuanji' AND FIND_IN_SET( 'liye', tolist ) ) ) 
			AND DATEDIFF( msgtime, NOW())>=-3
		ORDER BY
			msgtime asc  
			) mwma,(
		SELECT
			@i := -1,
			@j := null,
			@m :=null
		) tmp 
	) overTime 
WHERE
	last_type = 0
	AND to_type = 1 and new_over_time is not null 
	AND TIMEDIFF( msgtime, new_over_time )>= '01:00:00'  and  DATEDIFF( msgtime, NOW())=0



SELECT
        *
        FROM
            (
        SELECT
        @i AS last_type,
        @j AS last_time,
        @i := to_type,
        @j := msgtime,
        mwma.*
        FROM
        (
        SELECT
        *
        FROM
        me_wecom_msg_audit
        WHERE
        1 = 1
        AND ( ( msgfrom = 'liye' AND FIND_IN_SET( 'xuanji', tolist ) ) OR ( msgfrom = 'xuanji' AND FIND_IN_SET( 'liye', tolist ) ) )
        AND DATEDIFF( msgtime, NOW()) <= -3
        ORDER BY
        msgtime desc
        ) mwma,(
        SELECT
        @i := null,
        @j := null
        ) tmp
        ) overTime
        WHERE
         to_type = 0 and last_type=1
          and  DATEDIFF( msgtime, NOW()) =0