数据库-查询卷文件数

统计卷文件数

修改数据库配置
vim ./services/mysql/conf/mysqld.cnf
在[mysqld]节点新增配置：
group_concat_max_len = 102400

临时设置group_concat_max_len 参数：

SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400;

新增mysql函数:

/**
 * 查询目录子节点，
 * _case_id 案件ID
 * _directory_id 目录ID
 * _deleted true 不查询删除节点。否则查询忽略删除状态 
 */

CREATE DEFINER=`root`@`%` FUNCTION `dossier`.`get_sub_directory_ids`(_case_id bigint(20), _directory_id bigint(20),_deleted boolean) RETURNS varchar(8000) CHARSET utf8
BEGIN
    DECLARE ids varchar(8000) default '';
    DECLARE tmp_ids varchar(2000);
    SET tmp_ids = CAST(_directory_id as CHAR);
    while tmp_ids is not null do
        set ids = CONCAT(ids, ',', tmp_ids);
        if _deleted then
            SELECT GROUP_CONCAT(`directory_id`) INTO tmp_ids FROM archive_directory_info  where `case_id` = _case_id and `display` = 0 and  FIND_IN_SET(`parent_id` , tmp_ids) > 0;
        else
            SELECT GROUP_CONCAT(`directory_id`) INTO tmp_ids FROM archive_directory_info  where `case_id` = _case_id  and  FIND_IN_SET(`parent_id` , tmp_ids) > 0;
        end if;
    end while;
    RETURN SUBSTRING(ids, 2);
END

1. 查询卷文件数

   select a.directory_id,count(0) amount  from archive_directory_info a inner join 
   (
    select directory_name,max(case_id) as case_id  from archive_directory_info where case_id = 9527 and parent_id  = 0 and display = 0 group by directory_name  HAVING COUNT(0) > 0
   ) b 
   on a.case_id = b.case_id and a.directory_name = b.directory_name 
   inner join file_info fi on fi.case_id = a.case_id and fi.display = 0 and FIND_IN_SET(gd_dir_id,get_sub_directory_ids(a.case_id ,a.directory_id)) 
   group by a.directory_id 

2. 查询重复卷文件数

   select dir.directory_id,dir.directory_name,sum(if(file.file_id is null,0,1)) amount from 
   (
    select a.case_id,a.directory_id,a.directory_name,get_sub_directory_ids(a.case_id ,a.directory_id) as ids  from archive_directory_info a inner join 
    (
    -- 查询重复卷
        select directory_name,max(case_id) as case_id  from archive_directory_info where case_id = 9527 and parent_id  = 0 and display = 0 group by directory_name  HAVING COUNT(0) > 1
    ) b 
    on a.case_id = b.case_id and a.directory_name = b.directory_name 
   ) dir left join file_info file on dir.case_id=file.case_id and file.display = 0 and FIND_IN_SET(file.gd_dir_id, dir.ids) > 0
   group by dir.directory_id,dir.directory_name
   -- 查询文件数=0的卷
   having sum(if(file.file_id is null,0,1))=0