#20210125
    alter view v_person_bank as
    SELECT `t1`.`id`                          AS `id`,
           `t1`.`xm`                          AS `name`,
           `t1`.`idnum`                       AS `idnum`,
           ifnull(`tt`.`phone`, `t1`.`phone`) AS `phone`,
           `tt`.`name`                        AS `companyName`,
           `t1`.`zsbh`                        AS `cerNo`,
           `t1`.`fzrq`                        AS `cerDate`,
           `t1`.`areaCode`                    AS `areaCode`,
           ifnull(`t3`.`state`, '1')          AS `cardState`,
           `t3`.`freezeDate`                  AS `logoutDate`,
           ifnull(`tt`.`incumbencyStatus`, 0) AS `incumbencyStatus`,
           `t3`.`activeDate`                  AS `activeDate`,
           `t3`.`create_time`                 AS `bankDate`
    FROM `bank_account` `t3`
             left join `security_certificate` `t1` on `t1`.`idnum` = `t3`.`idNum` and `t3`.`active` = TRUE
             left join (select `t5`.`name`             AS `name`,
                               `t2`.`id_number`        AS `id_number`,
                               `t2`.`phone`            AS `phone`,
                               `t2`.`incumbencyStatus` AS `incumbencyStatus`
                        from `security_person` `t2`
                                 join `security_company_person` `t4`
                                      on `t2`.`id` = `t4`.`person_id` and `t4`.`active` = TRUE
                                 join `security_company` `t5`
                                      on `t4`.`company_id` = `t5`.`id` and `t5`.`active` = TRUE
                        where `t2`.`active` = TRUE
                          and `t2`.`incumbencyStatus` = 1) `tt`
                       on `t1`.`idnum` = `tt`.`id_number`
    where `t1`.`active` = TRUE
      and ifnull(`t1`.`state`
              , 1) = 1
      and if(isnull(`t3`.`create_time`)
              , 0
              , 1) = 1;