近期工作中的一些sql总结

近期工作中遇到的一些sql,总结如下文:

一、update结合inner join使用来“刷”历史数据

-- 更新还款业务单中的银行编码和银行卡号
UPDATE order_bind_deduct o
 INNER JOIN user_info_extend a ON o.account_no = a.account_no
 SET o.bank_code = a.bank_code,
 o.bankcard_no = a.bankcard_no;

二、delete结合inner join使用来删除重复数据

-- 只保留各个部门id最小的那条员工数据,其余的都删掉
DELETE s
FROM
	employee s
INNER JOIN (
	SELECT
		min(t.id) min_id,
		t.dept_id dept_id
	FROM
		employee t
	GROUP BY
		t.dept_id
	HAVING
		count(t.id) > 1
) tmp ON s.dept_id = tmp.dept_id
WHERE
	s.id <> tmp.min_id;

三、使用json_extract和json_replace函数来修复表中的json数据

由于同事对人脸识别返回的图片链接拼接有误,少了一截,需要将这些数据修复,补上这一截’ocrmuyun’

人脸识别返回的图片链接存储在authen_record表中的ext_data字段中,是以json串的形式进行存储的,格式如下:

{
    "msg":"成功",
    "code":0,
    "data":{
        "status":"OK",
        "verifyResult":{
            "image":"http://pir.cloud.hinner/ocrmuyun/face_16055938103804.jpeg",
            "result":"PASS",
            "idExceptions":{
                "idAttacked":0,
                "idPhotoMonochrome":0
            },
            "resultFaceId":{
                "confidence":88.111,
                "thresholds":{
                    "1e-3":62.169,
                    "1e-4":69.315,
                    "1e-5":74.399,
                    "1e-6":78.038
                }
            }
        },
        "livenessResult":{
            "result":"PASS"
        }
    }
}

从mysql 5.7以后,开始支持json解析函数,所以如果版本支持,可以考虑用json相关的函数来处理

mysql官网json相关函数

UPDATE
  authen_record
SET
  ext_data = JSON_REPLACE(
    ext_data,
    '$.data.verifyResult.image',
    REPLACE(
          JSON_UNQUOTE(
             JSON_EXTRACT(
                ext_data,
                '$.data.verifyResult.image'
              )
          ),
         'http://pir.cloud.hinner',
         'http://pir.cloud.hinner/ocrmuyun'
     )
 )
WHERE id IN (
SELECT
  a.id
FROM
  (SELECT
    id,
    JSON_UNQUOTE(
      JSON_EXTRACT(
        ext_data,
        '$.data.verifyResult.image'
      )
     )
     image_url
  FROM
    authen_record) a
WHERE a.image_url IS NOT NULL
  AND a.image_url NOT LIKE 'http://pir.cloud.hinner/ocrmuyun/%'
  )
  AND face_type=0;
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页