前言

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

一、导出列表的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，则返回第二个参数的备用值。

③实例
第一个参数为 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
	

3. 改变分隔符。
   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

4. 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'