Mysql常用方法

将两个字段拼接成一个字段方法concat函数：

将日期转换为只要某一段 time_format(时间字段,要转换时间类型)函数:

两个字段数值运算,直接使用运算符,如果一个字段为null使用conalesce(字段,替换成什么数值)函数进行替换为你想要的值

SELECT 
  t.training_date AS trainingDate,
  t.subject AS subject,
  t.training_id AS trainingId,
  CONCAT(TIME_FORMAT(t.start_time, '%H:%i'), '~', TIME_FORMAT(t.end_time, '%H:%i')) AS trainingTime,
  t.capacity - COALESCE(t.appointment_count, 0) AS residueNum,
  t.course_location AS courseLocation
FROM training_schedule t;

在一个查询出来的表格统计某一个字段的总值 totalTodo就是那个新创建的总值  '10000' AS bmRegion,'汇总' AS `region`,给该条数据添加默认值

SELECT
    gnCoding,
    bmRegion,
    `region`,
    total,
    SUM(totalTodo) AS totalTodo
FROM (
    SELECT
        gnCoding,
        bmRegion,
        `region`,
        total,
        totalTodo
    FROM (
        SELECT
            x.jjrj_gn_coding_jjrj AS gnCoding,
            e.bm_region AS bmRegion,
            e.region AS `region`,
            COUNT(e.jjrj_qy_name_jjrj) AS total,
            SUM(IF(x.jjrj_audit_jjrj < 10000 AND x.jjrj_audit_jjrj > 0, 1, 0)) AS totalTodo
        FROM
            jcxx_personnelinformation x
        INNER JOIN
            enterprise_practice_area e ON x.jjrj_qy_bianma_jjrj = e.jjrj_qy_bianma_jjrj
        GROUP BY
            x.jjrj_gn_coding_jjrj,
            e.bm_region
        UNION ALL
        SELECT
            x.jjrj_gn_coding_jjrj AS gnCoding,
            '10000' AS bmRegion,
            '汇总' AS `region`,
            COUNT(e.jjrj_qy_name_jjrj) AS total,
            SUM(IF(x.jjrj_audit_jjrj < 10000 AND x.jjrj_audit_jjrj > 0, 1, 0)) AS totalTodo
        FROM
            jcxx_personnelinformation x
        INNER JOIN
            enterprise_practice_area e ON x.jjrj_qy_bianma_jjrj = e.jjrj_qy_bianma_jjrj
    ) AS subquery
) AS final_result
GROUP BY gnCoding, bmRegion, `region`, total;

原代码

SELECT
    x.jjrj_gn_coding_jjrj AS gnCoding,
    e.bm_region AS bmRegion,
    e.region AS `region`,
    COUNT(e.jjrj_qy_name_jjrj) AS total,
    SUM(IF(x.jjrj_audit_jjrj < 10000 AND x.jjrj_audit_jjrj > 0, 1, 0)) AS totalTodo
FROM
    jcxx_personnelinformation x
INNER JOIN
    enterprise_practice_area e ON x.jjrj_qy_bianma_jjrj = e.jjrj_qy_bianma_jjrj
GROUP BY
    x.jjrj_gn_coding_jjrj,
    e.bm_region;