MySQL查询中复杂函数使用

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

MySQL复杂函数使用

前言

最近接到一个导出业务数据的需求遇见了一些问题最后总结了一些函数希望对大家有帮助。

一、导出列表的sql

  SELECT
    	IFNULL(cdr.id,"") as id,
    	IFNULL(cdcr.create_user,"") as clearRecordUser,
        cc.batch_name AS batchName,
        CONCAT(cc.start_time,'-',cc.end_time) AS deviceCheckTimeRange,
        cdr.create_user AS deviceCheckUser,
        CONCAT(cdr.combat_unit,'-',cdr.combat_department,'-',cdr.combat_sub_department) AS deviceCheckDept,
        cdr.finish_time AS finishTime,
        cdr.device_asset_code AS deviceAssetCode,
        cdr.device_only_code AS deviceOnlyCode,
        cdr.device_model AS deviceModel,
        cdr.device_type AS deviceType,
        cdr.device_owned_dealer_code AS deviceDealerCode,
        cdr.device_owned_dealer_name AS deviceDealerName,
        cdr.combat_unit AS combatUnit,
        cdr.combat_department AS combatDepartment,
        cdr.combat_sub_department AS combatSubDepartment,
        case cdr.device_check_status when 'checking' then '盘点中' when 'checked' then '盘点完成' when 'check_error' then '盘点异常' end AS deviceCheckStatus,
        (SELECT count(icdr.device_only_code) from t_gos_inventory_check_device_record icdr where icdr.device_only_code =cdr.device_only_code GROUP BY icdr.device_only_code )  AS deviceCheckCumulativeNumber,
        case ifnull(temp2.lastFinishTime,'N') when 'N' then '' else datediff(cdr.finish_time ,(SELECT icdr.finish_time FROM t_gos_inventory_check_device_record icdr WHERE icdr.device_only_code = cdr.device_only_code and icdr.batch_id = temp2.lastBatchId)) END AS lastDeviceCheckNumber,
        group_concat(case (	SELECT err.error_reason from   t_gos_inventory_check_device_error_record err where err.id in (temp2.device_check_error_record_list)) when 'address_error' then '盘点地址异常' when 'owned_dealer_error' then '设备归属经销商异常' when 'status_not_in_system_error' then '非在库状态异常' when 'in_system_not_check_error' then '在库未盘点异常' end)  AS lastErrorReason,
        case (SELECT icdr.device_check_status FROM t_gos_inventory_check_device_record icdr WHERE icdr.device_only_code = cdr.device_only_code and icdr.batch_id = temp2.lastBatchId) when 'checking' then '盘点中' when 'checked' then '盘点完成' when 'check_error' then '盘点异常' end AS lastDeviceCheckStatus,
        cdr.device_check_warehouse_address AS deviceCheckWarehouseAddress,
        cdr.device_check_address AS deviceCheckAddress,
        case cdr.device_check_address_verify when 'check_success' then '核验成功' when 'check_failure' then '核验失败' end AS deviceCheckAddressVerify,
        case (SELECT ica.audit_status from t_gos_inventory_check_attachment ica   where  ica.attachment_type =
        'environment' and ica.check_record_id = icr.id LIMIT  1) when 'audit_todo' then '待审核' when 'audit_success' then '核验通过' when 'audit_failure' then '核验失败' end as environmentAuditStatus,
        case (SELECT ica.audit_status from t_gos_inventory_check_attachment ica where ica.attachment_type =
        'device_asset' and ica.check_record_id = cdr.id LIMIT  1) when 'audit_todo' then '待审核' when 'audit_success' then '核验通过' when 'audit_failure' then '核验失败' end as deviceAssetAuditStatus,
        case (SELECT ica.audit_status from t_gos_inventory_check_attachment ica where ica.attachment_type =
        'device_front' and ica.check_record_id = cdr.id LIMIT  1) when 'audit_todo' then '待审核' when 'audit_success' then '核验通过' when 'audit_failure' then '核验失败' end as deviceFrontAuditStatus,
        (SELECT ica.url_list from t_gos_inventory_check_attachment ica   where  ica.attachment_type = 'environment' and ica.check_record_id = icr.id LIMIT  1) as environment,
        (SELECT ica.url_list from t_gos_inventory_check_attachment ica where ica.attachment_type = 'device_asset' and ica.check_record_id = cdr.id LIMIT  1) as deviceAsset,
        (SELECT ica.url_list from t_gos_inventory_check_attachment ica where ica.attachment_type = 'device_front' and ica.check_record_id = cdr.id LIMIT  1) as deviceFront
        FROM
        t_gos_inventory_check_device_record cdr
        LEFT JOIN t_gos_inventory_check_config cc ON cdr.batch_id = cc.id
        LEFT JOIN t_gos_inventory_check_device_error_record cder ON cdr.id = cder.device_record_id
        LEFT JOIN t_gos_inventory_check_record icr on  icr.batch_id = cdr.batch_id and icr.dealer_code = cdr.owned_dealer_code and icr.ware_house_id = cdr.ware_house_id
        LEFT JOIN
        (SELECT
        temp1.id,
        temp1.lastBatchId,
        temp1.device_only_code,
        (SELECT icdr.finish_time FROM t_gos_inventory_check_device_record icdr WHERE icdr.device_only_code = temp1.device_only_code and icdr.batch_id = temp1.lastBatchId) AS lastFinishTime,
        (SELECT REPLACE(REPLACE(icdr.device_check_error_record_list,'[',''),']','') FROM	t_gos_inventory_check_device_record icdr where  icdr.device_only_code = temp1.device_only_code and icdr.batch_id = temp1.lastBatchId) as device_check_error_record_list
        from
        (SELECT cdr.id,cdr.device_only_code,(SELECT cc.id from t_gos_inventory_check_config cc WHERE cc.id    <   cdr.batch_id and  cc.status ='enable' order by cc.id desc limit 1) lastBatchId FROM t_gos_inventory_check_device_record cdr GROUP BY cdr.id) temp1 ) temp2 on temp2.id=cdr.id
        GROUP BY cdr.id
        ORDER BY cdr.batch_id DESC,cdr.finish_time;

二、函数

2.1IFNULL()

① 描述用于判断第一个表达式是否为 NULL如果为 NULL 则返回第二个参数的值如果不为 NULL 则返回第一个参数的值。

② 语法格式为

IFNULL(expression, alt_value)

如果第一个参数的表达式 expression 为 NULL则返回第二个参数的备用值。

参数描述
expression必须要测试的值
alt_value必须expression 表达式为 NULL 时返回的值

③实例
第一个参数为 NULL

SELECT IFNULL(NULL, "RUNOOB");

以上实例输出结果为RUNOOB

第一个参数不为 NULL

SELECT IFNULL("Hello", "RUNOOB");

以上实例输出结果为Hello

2. 2CONCAT()

①描述CASE 表示函数开始END 表示函数结束。如果 condition1 成立则返回 result1, 如果 condition2 成立则返回 result2当全部不成立则返回 result而当有一个成立之后后面的就不执行了。
② 语法格式为

CONCAT(s1,s2...sn)

字符串 s1,s2 等多个字符串合并为一个字符串
③实例

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;

2.3CASE WHEN

①描述CASE 表示函数开始END 表示函数结束。如果 condition1 成立则返回 result1, 如果 condition2 成立则返回 result2当全部不成立则返回 result而当有一个成立之后后面的就不执行了。
② 语法格式为

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

③实例

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

SELECT case cdr.device_check_status when 'checking' then '盘点中' when 'checked' then '盘点完成' when 'check_error' then '盘点异常' end AS deviceCheckStatus;

2.4REPLACE()

①描述将字符串 s2 替代字符串 s 中的字符串 s1
② 语法格式为

REPLACE(s,s1,s2)

③实例
将字符串 abc 中的字符 a 替换为字符 x

SELECT REPLACE('abc','a','x') --xbc

去掉[]

SELECT REPLACE(REPLACE(icdr.device_check_error_record_list,'[',''),']','') ;

2.5group_concat()

①描述用来合并字段使用主要用来处理一对多的查询结果通常会结合GROUP BY一起使用。
② 语法格式为

GROUP_CONCAT([DISTINCT] expr [,expr ...]

             [ORDER BY {unsigned_integer | col_name | expr}

                 [ASC | DESC] [,col_name ...]]

             [SEPARATOR str_val])

③实例

SELECT
    s.stu_id          AS  studentId,
    s.stu_name     AS  studentName,
    GROUP_CONCAT(c.course_id)    AS  courseId,
    GROUP_CONCAT(c.course_name)  AS  studentCourse
FROM
    student s
LEFT JOIN
    stu_course sc
ON
    s.stu_id = sc.stu_id
LEFT JOIN
    course c
ON
    sc.course_id = c.course_id
GROUP BY
    studentId

  1. 使用GROUP_CONCAT时会将数值类型的数据转化成二进制BLOB类型可以用CAST(expr AS type)函数或CONVERT(expr, type)函数将数值类型的数据转化成字符串:

  2. 若要将第一条记录中courseId按照从小到大的方式归集则可以在使用GROUP_CONCAT() 时加上ORDER BY

SELECT
    s.stu_id          AS  studentId,
    s.stu_name     AS  studentName,
    GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id) AS courseId,
    GROUP_CONCAT(c.course_name)  AS  studentCourse
FROM
    student s
LEFT JOIN
    stu_course sc
ON
    s.stu_id = sc.stu_id
LEFT JOIN
    course c
ON
    sc.course_id = c.course_id
GROUP BY
    studentId
  1. 改变分隔符。
    GROUP_CONCAT默认的分隔符是逗号”,”若想换成其他分隔符可以用 SEPARATOR关键字
SELECT
    s.stu_id          AS  studentId,
    s.stu_name     AS  studentName,
    GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id SEPARATOR '/')    AS  courseId,
    GROUP_CONCAT(c.course_name SEPARATOR '|')   AS  studentCourse
FROM
    student s
LEFT JOIN
    stu_course sc
ON
    s.stu_id = sc.stu_id
LEFT JOIN
    course c
ON
    sc.course_id = c.course_id
GROUP BY
    studentId
  1. GROUP_CONCAT长度限制
    用了GROUP_CONCAT后SELECT里如果使用了LIMIT是不起作用的.
    用GROUP_CONCAT连接字段的时候是有长度限制的并不是有多少连多少。但可以设置一下。
    使用group_concat_max_len系统变量可以设置允许的最大长度。
    语法如下其中 val 是一个无符号整数
    SET [SESSION | GLOBAL] group_concat_max_len = val;
    若已经设置了最大长度 则结果被截至这个最大长度。

2.6 json_length()

①描述返回json文档的长度
② 语法格式为

JSON_LENGTH(json_doc[, path])
返回数组的长度如果是object则是属性个数常量则为1

③实例

SELECT a.id as id,
               a.batch_id,
               a.device_asset_code,
               a.device_only_code,
               a.device_check_error_record_list,
               a.device_check_error_audit_status
        FROM t_gos_inventory_check_device_record a
                 inner join t_gos_inventory_check_device_error_record b
                            on (json_length(device_check_error_record_list) = 1 and
                                a.device_check_error_record_list -> '$[0]' = b.id);

select json_length('[1,2,3]')
结果3

select json_length('123')
结果1

select json_length('{"a":1,"b":2}')
结果2

可再跟path参数
select json_length('{"a":1,"b":[1,2,3]}','$.b')
结果3

三、很重要的总结

sql语句只是用来做查询的这些数据处理的逻辑应当放到Java代码里面处理。
关联查询应该不超过三张表然后注意走索引。

    SELECT
            cdr.id as id,
            cdr.batch_id as batchId,
            cdr.updated_at AS updatedAt,
            cdr.device_asset_code as deviceAssetCode,
            cdr.device_only_code as deviceOnlyCode,
            cdr.device_owned_dealer_name as deviceDealerName,
            cdr.device_owned_dealer_code as deviceDealerCode,
            cdr.combat_unit as combatUnit,
            cdr.combat_department as combatDepartment,
            cdr.combat_sub_department as combatSubDepartment,
            cdr.sale_group as saleGroup,
            cdr.ware_house_name as wareHouseName,
            cdr.create_user as deviceCheckUser,
            cdr.device_check_address as deviceCheckAddress,
            cdr.device_check_warehouse_address as deviceCheckWarehouseAddress,
            cdr.device_check_address_verify as  deviceCheckAddressVerify,
            cdr.device_check_status as deviceCheckStatus,
            cdr.device_check_audit_status as deviceCheckAuditStatus,
            cdr.finish_time as finishTime,
            cdr.device_status as deviceStatus,
            cdr.device_type as deviceType
        FROM
        t_gos_inventory_check_device_record cdr
        LEFT JOIN t_gos_inventory_check_record cr on  cr.batch_id = cdr.batch_id and cr.dealer_code = cdr.owned_dealer_code and cr.ware_house_id = cdr.ware_house_id
        where
            cdr.device_check_error_audit_status in ('not_error','error_audited','error_patching','error_invalid')
            and cr.check_status ='checked'
阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql
返回列表

上一篇：SLS：基于 OTel 的移动端全链路 Trace 建设思考和实践

下一篇：shell 脚本中 的＞ /dev/null 2＞&1

“MySQL查询中复杂函数使用” 的相关文章

《MySQL高级篇》十、数据库其他调优策略1年前 (2023-02-02)
MySQL表的高级增删改查1年前 (2023-02-02)
javaEE学生教学实习计划申报系统Myeclipse开发mysql数据库web结构jsp编程计算机网页项目1年前 (2023-02-02)
MySQL基础——多表查询1年前 (2023-02-02)
【MySQL】(图解)快速理解内连接、外连接、左连接、右连接1年前 (2023-02-02)
【大数据系列之MySQL】（二十七）：子查询的经典使用案例1年前 (2023-02-02)
MySQL主备切换1年前 (2023-02-02)
jdbc连接mysql简单实例，在java项目中添加数据库驱动包1年前 (2023-02-02)
mysql查看、修改数据库字符集1年前 (2023-02-02)
MySQL：约束概述、约束演示、外键约束1年前 (2023-02-02)

阿里云国际版