SQL分类查询并统计，使用 coalesce(）函数等

纪念一下最近写的“庞大” sql ！这个sql是关于数量分类统计的，总结一下所用到函数。

    /**
     * 第三方系统拥有证书统计 查询
     *
     * @param customerSysName
     * @param startTime
     * @param endTime
     * @return
     */
    public List<StatisticsCustomerDTO> getCustomerCertCount(String customerSysName, String startTime, String endTime) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        String nameSelectSql = "";
        String nameSelectSql2 = "";
        String timeSelectSql = "";
        if (StringUtils.isNotBlank(customerSysName)) {
            nameSelectSql = " WHERE a.customer_sys_name like :customerSysName ";
            nameSelectSql2 = " AND c.customer_sys_name like :customerSysName ";
            params.addValue("customerSysName",  "%" + customerSysName + "%");
        }
        if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
            timeSelectSql = "AND a.gmt_create >= :startTime AND a.gmt_create <= :endTime ";
            params.addValue("startTime", startTime + " 00:00:00");
            params.addValue("endTime", endTime + " 23:59:59");
        }
        String selectSql = "SELECT  t.system_flag AS customerSysNumber , c.customer_sys_name AS customerSysName, t.normal, t.outdate, t.revoked, t.total\n" +
                "FROM\n" +
                "     (SELECT coalesce(system_flag,'合计') AS system_flag, SUM(normal) normal, SUM(outdate) outdate, SUM(revoked) revoked, SUM(total) total\n" +
                "            FROM\n" +
                "\n" +
                "            (SELECT a.customer_sys_number AS system_flag, a.customer_sys_name  AS customerSysName,0 normal, 0 outdate, 0 revoked, 0 total\n" +
                "\t\t\t\t\t\t\t\t\tFROM customer_sys a \n" +
                nameSelectSql +
                "\n" +
                "\t\t\t\t\t\t\t\t\tUNION ALL\n" +
                "             SELECT system_flag AS systemflag, customerSysName, COUNT(IF(cert_status = 1 AND failure_time > NOW() , 1, NULL)) normal, COUNT(IF(failure_time <= NOW() AND cert_status != 0, 1, NULL)) outdate,\n" +
                "\t\t\t\t\t\t\t\t\t  COUNT(IF(cert_status = 3 AND failure_time > NOW(), 1, NULL)) revoked, COUNT(IF(cert_status != 0 , 1, NULL)) total\n" +
                "             FROM\n" +
                "\t\t\t\t\t\t\t(SELECT b.system_flag ,a.cert_status, c.customer_sys_name AS customerSysName,a.failure_time\n" +
                "\t\t\t\t\t\t\t FROM user_cert a, base_user b, customer_sys c \n" +
                "\t\t\t\t\t\t\t WHERE b.id = a.user_id  \n" +
                timeSelectSql +
                "\t\t\t\t\t\t\t AND c.customer_sys_number = b.system_flag\n" +
                nameSelectSql2 +
                "\t\t\t\t\t\t\t ) AS d\n" +
                "\t\t\t\t\t\t\t GROUP BY system_flag\n" +
                "\t\t\t\t\t\t)AS e\n" +
                "\t\t\t\t\t\t \n" +
                "            GROUP BY system_flag WITH ROLLUP ) AS t\n" +
                "LEFT JOIN customer_sys AS c  ON c.customer_sys_number = t.system_flag \n" +
                "\n " ;
        logger.info("selectSql========" + selectSql);
        return this.daoTemplate.queryForList(selectSql, params, BeanPropertyRowMapper.newInstance(StatisticsCustomerDTO.class));

    }

大概查询过程：通过user_cert的user_id关联base_user的id查询出cert对应的system_flag，按照system_flag分类查询 user_cert表中不同状态证书的数量并统计，再到customer_sys表中查询customer_sys_number对应的customer_sys_name进行展示。

system_flag的值大于customer_sys_number。

nameSelectSql 为按customer_sys_name模糊查询。

timeSelectSql 时间筛选查询。

查询结果展示如下：

涉及函数：

NOW()   获取当前时间

SELECT NOW(), CURDATE(), CURTIME()

结果：2019-11-19 17:21:49    2019-11-19    17:21:49

coalesce(expr1,expr2) 

coalesce(customer_sys_number,'合计')

如果customer_sys_number为空返回'合计'

coalesce(customer_sys_number)

如果customer_sys_number为空返回空

IF(expr1,expr2,expr3)  IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

IF(a.cert_status = 1 AND a.failure_time > NOW() , 1, NULL)

如果满足a.cert_status = 1 AND a.failure_time > NOW() 则计为1，负责返回空