SQL数据统计总结

写复杂的sql语句时，尽量别用分页  count(0)出现可能使用分页

 1、统计本级单位以及下级数据

方法一：先子表用count函数统计数据  再配合find_in_set函数 、sum函数统计全部数据。count统计本级数据，sum配合find_in_set是同统计本级以及子级的数据

方法二：直接使用count配合find_in_set再加上判断FIND_IN_SET( dept.id, t.parent_path )来统计本级和本级及其下级数据

SELECTdept.id,//本级数据count( CASE WHEN FIND_IN_SET( dept.id, t.parent_path )= 1 THEN 1 END ) bdw,//本级数据及下级数据count( CASE WHEN FIND_IN_SET( dept.id, t.parent_path )> 0 THEN 1 END ) lj FROMsys_department deptLEFT JOIN (SELECTtq.org_Id,sd.parent_path,sd.id deptId FROM`tq_meet` tqLEFT JOIN sys_department sd ON tq.org_id = sd.id ) t ON FIND_IN_SET( dept.id, t.parent_path )> 0 GROUP BYdept.id

 先用count统计数量再用sum计算和

  select fresult.*        from (        select sd.id ,sd.`name` deptName,        IFNULL(sum(result.PersuasiveNum),0) PersuasiveNum,        IFNULL(sum(result.DsrgzdwNum),0) DsrgzdwNum        from        sys_department sd        left join (        select dTable.create_dept_id,dept.parent_path,dTable.PersuasiveNum PersuasiveNum,dTable.DsrgzdwNum DsrgzdwNum        from (        SELECT        td.create_dept_id,        count( td.create_dept_id ) PersuasiveNum,        count(case when td.dsr_gzdw IS NOT NULL        and td.dsr_gzdw != '' then 1 end) DsrgzdwNum        FROM        tq_ddcwf td                GROUP BY        td.create_dept_id        )dTable        left join sys_department dept on dTable.create_dept_id =dept.id        ) result on FIND_IN_SET(sd.id,result.parent_path)>0        where sd.id is not null and sd.status=1        GROUP BY sd.id        ) fresult        order by fresult.PersuasiveNum desc

3、传入部门id查出本级及其所以下级的部门id 用来实现统计数据  最好添加上

GROUP BY td.create_dept_id来分类

          and td.create_dept_id in        (select id from        (        with recursive departments as        (        select a.id,a.name,a.parent_id,b.name as parent_name        from sys_department a        left join sys_department b on a.parent_id=b.id        where a.id in (${deptId})        union all        select bb.id,bb.name,bb.parent_id,bb.parent_name from        (        select a.id,a.name,a.parent_id,b.name as parent_name        from sys_department a        left join sys_department b on a.parent_id=b.id        ) as bb,departments as h where h.id=bb.parent_id        )        select dep.id,dep.name,dep.parent_id,dep.parent_name from departments as dep        ) xx        )      

 此方法的前提是表有parent_path这个字段，以下为生成此字段

//更新所有单位的上级路径sysDepartmentMapper.updataParentPath(new HashMap());//mapper文件int updataParentPath(HashMap map);//xml代码    update sys_department sd    set sd.parent_path =getParentList('sys_department', sd.id)

 getParentList为数据库中的函数。

CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(tableName varchar(64),rootId varchar(100)) RETURNS varchar(1000) CHARSET utf8BEGIN DECLARE fid varchar(100) default ''; DECLARE str varchar(4000) default rootId;  IF tableName = 'sys_department' THEN WHILE rootId is not null  do SET fid =(SELECT parent_id FROM sys_department WHERE id = rootId); IF fid is not null THEN SET str = concat(str, ',', fid); SET rootId = fid; ELSE SET rootId = fid; END IF; END WHILE; END IF; return str;END

 update sys_department sd

        set sd.parent_path =getParentList('sys_department', sd.id)

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'str' at row 1

当数据库中数据父子结构乱的时候可用以下查出结构混乱的数据并删除

SELECT * FROM sys_department depart LEFT JOIN sys_department sd2 on depart.parent_id=sd2.idLEFT JOIN sys_department sd3 on sd2.parent_id=sd3.idLEFT JOIN sys_department sd4 on sd3.parent_id=sd4.idLEFT JOIN sys_department sd5 on sd4.parent_id=sd5.idLEFT JOIN sys_department sd6 on sd5.parent_id=sd6.idWHERE depart.id=sd2.id or depart.id=sd3.id or depart.id=sd4.id or depart.id=sd5.id or depart.id=sd6.id 

 统计还有生成临时表在统计

  CREATE TEMPORARY TABLE IF NOT EXISTS tempresult AS (    select count(CASE WHEN YEARWEEK(td.create_time,1) = YEARWEEK(now(),1)-1 THEN 0 END) lastWeekSum,    count(CASE WHEN YEARWEEK(td.create_time,1) = YEARWEEK(now(),1) THEN 0 END) thisWeekSum,    count(CASE WHEN MONTH(td.create_time) = MONTH(now()) THEN 0 END) thisMonthSum,    td.create_dept_name createDeptName,    td.create_dept_id  createDeptId    FROM tq_ddcwf td          td.cllx='面包车'      and (MONTH(td.create_time) = MONTH(now()) or YEARWEEK(td.create_time,1) = YEARWEEK(now(),1)-1 or YEARWEEK(td.create_time,1) = YEARWEEK(now(),1))      and td.hzrs>4              and td.create_dept_id in        (select id from        (        with recursive departments as        (        select a.id,a.name,a.parent_id,b.name as parent_name        from sys_department a        left join sys_department b on a.parent_id=b.id        where a.id in (${deptId})        union all        select bb.id,bb.name,bb.parent_id,bb.parent_name from        (        select a.id,a.name,a.parent_id,b.name as parent_name        from sys_department a        left join sys_department b on a.parent_id=b.id        ) as bb,departments as h where h.id=bb.parent_id        )        select dep.id,dep.name,dep.parent_id,dep.parent_name from departments as dep        ) xx        )        GROUP BY td.create_dept_id              );    SELECT    IFNULL( SUM( t.lastWeekSum ), 0 ) AS lastWeekSum,    IFNULL( SUM( t.thisWeekSum ), 0 ) AS thisWeekSum,    IFNULL( SUM( t.thisMonthSum ), 0 ) AS thisMonthSum,    sys.NAME AS createDeptName    FROM    sys_department sys    RIGHT JOIN (    SELECT    temp1.lastWeekSum,    temp1.thisWeekSum,    temp1.thisMonthSum,    temp1.createDeptName,    temp1.createDeptId,    sd.parent_path,    sd.parent_id    FROM    tempresult temp1    LEFT JOIN sys_department sd ON sd.id = temp1.createDeptId    ) t ON FIND_IN_SET( sys.id, t.parent_path )> 0                  sys.parent_id is null or sys.parent_id =''              GROUP BY    sys.id;

2、根据日期统计统计多张表

WITH recursive dates AS (  SELECT DATE('开始时间') AS date  UNION ALL  SELECT date + INTERVAL 1 DAY  FROM dates  WHERE date  
实战
 
WITH recursive dates AS (  SELECT DATE('2023-08-15') AS date  UNION ALL  SELECT date + INTERVAL 1 DAY  FROM dates  WHERE date  
 
日期中没有的数据也会实现为0
 
3、通过构造mybatis返回类型结构来实现多表查询一条数据中带一个list数据
    
                                                                                                                select    cpr.id, cpr.zgr_id, cpr.zgr_xm, cpr.clzt, cpr.pfsj, cpr.cljg, cpr.cldz, cpr.create_user_id, cpr.create_user_name, cpr.create_time,    cpr.cp_id, cpr.bz, cpr.shjg, cpr.shyj, cpr.shr_id, cpr.shr_xm, cpr.shr_dept_id, cpr.shr_dept_name,    img.id img_id,CONCAT(#{httpUrl,jdbcType=VARCHAR},img.zplj ) img_zplj,img.zplx  img_zplx,img.ywbid img_ywbid    from cg_claim_parts_rectify cpr    left join tq_image img on cpr.id=img.ywbid                  and  cpr.cp_id=#{cpId,jdbcType=VARCHAR}            
 
上面使用嵌套结果映射 但分页不支持嵌套结果映射可以修改为嵌套select可参考
 
分页导致Mybatis数据不一致(使用了关联查询collection)_高树悲风的博客-CSDN博客
 
 4、CREATE TEMPORARY TABLE与with as 提取临时表别名
  
CREATE TEMPORARY TABLE 创建的临时表是一个独立的表对象，会在当前会话结束或显式删除表时被销毁。这意味着，临时表只在当前会话中可见，并且不能被其他会话访问。因此，您可以使用相同的表名创建临时表，在不同的会话中保证不会发生冲突。
 
另一方面，WITH 子句生成的临时表仅在查询语句的上下文中存在，也称为 "公用表表达式" (Common Table Expression, CTE)。这意味着，虽然临时表在查询中被引用了多次，但它只是查询中的一个临时结果集，并没有被创建为一个独立的表对象。因此，它不能在查询之外的上下文中被引用或操作。
 
因此，如果您需要在当前会话中创建一个独立的临时表，以供多个查询或操作使用，那么 CREATE TEMPORARY TABLE 将是更适合的选择。如果您只需要在一个查询中使用临时表，那么 WITH 子句创建的临时表将更加方便。
 
CREATE TEMPORARY TABLE a1 AS (    SELECT zh.*    FROM `user2023` zh    LEFT JOIN sys_user suer ON zh.accounts = suer.user_name    WHERE suer.id IS NULL);INSERT INTO user_copy (id, user_name, name, status, user_type, platform, password)SELECT UUID(), accounts, name, 1, '1', '1', 'dezhoujiaojing@123'FROM a1;INSERT INTO user_role (id, user_id, role_id, role_name)SELECT UUID(), c.id,  '1', '1'FROM ( SELECT b.id FROM a1 a LEFT JOIN user_copy b on a.accounts = b.user_name) c;-- DROP TEMPORARY TABLE IF EXISTS a1;
  
5、详细地址如何在表中查出所在乡镇（匹配查询）
 
 WITH town AS (      SELECT *FROM sys_department WHERE unit_type = 1 AND xz_level = 4 and xzqh=#{xzqh}    )    SELECT * FROM town WHERE #{djzs} LIKE CONCAT('%', town.name, '%');
 
6、在使用不能分组情况需要去重统计数据
  
 COUNT 函数在默认情况下不统计 NULL 值。如果你想统计包括 NULL 值在内的记录数量，可以使用 COUNT(*) 或 COUNT(字段名)。 所以可以设置为null排除
 
 count(DISTINCT case when (t.task_record_id and t.result =1) then t.task_record_id else NULL end ) as feedbackSum
 
mysql sum函数配合 case when distinct 去重复求和_sum case when 去掉重复值-CSDN博客
 
 7、使用索引的经验
 
a、子查询使索引失效
 
b、表连接时使用where才能使用索引