SQL数据统计总结

Posted on by hackdl

写复杂的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 )来统计本级和本级及其下级数据

SELECT
	dept.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 
FROM
	sys_department dept
	LEFT JOIN (
	SELECT
		tq.org_Id,
		sd.parent_path,
		sd.id deptId 
	FROM
		`tq_meet` tq
		LEFT JOIN sys_department sd ON tq.org_id = sd.id 
	) t ON FIND_IN_SET( dept.id, t.parent_path )> 0 
GROUP BY
	dept.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 utf8
BEGIN 
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.id
LEFT JOIN sys_department sd3 on sd2.parent_id=sd3.id
LEFT JOIN sys_department sd4 on sd3.parent_id=sd4.id
LEFT JOIN sys_department sd5 on sd4.parent_id=sd5.id
LEFT JOIN sys_department sd6 on sd5.parent_id=sd6.id
WHERE 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、with as 提取临时表别名

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: