mysql 数据库统计出现误差, 在spring的调度器和程序逻辑都正常的情况下, 可以使用如下方法

本文链接：https://blog.csdn.net/avgworkld/article/details/53018114

1,查看调度器里面的配置, 查到数据的根位置,可以直接写储存过程来对统计表进行重新统计,, 如下, 我只贴一个例子--->

统计表能直接用联合查询可以把字段列出的:::------.>如下

DELIMITER $$

USE `bbbb`$$

DROP PROCEDURE IF EXISTS `A0_history_user_gdata_statistics_total_count_by_age_range_proc`$$

CREATE DEFINER=`root`@`%` PROCEDURE `A0_history_user_gdata_statistics_total_count_by_age_range_proc`()
BEGIN
DECLARE vId VARCHAR (50) ;
DECLARE vOrgId INTEGER (50) ;
DECLARE vOrgPath VARCHAR (40) ;
DECLARE vTime DATETIME ;
DECLARE vQuantity INT ;
DECLARE vAgeRange INTEGER(2) ;
-- 遍历数据结束标志
DECLARE done INT DEFAULT 0;
DECLARE iCount INT ;
DECLARE cur CURSOR FOR
SELECT
u.`organizationId`,
o.`organizationPath`,
MAX(u.`CREATE_TIME`) creatTime,
CASE
WHEN (DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) < 18 THEN 1
WHEN ((DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) BETWEEN 18 AND 29) THEN 2
WHEN ((DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) BETWEEN 30 AND 39) THEN 3
WHEN ((DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) BETWEEN 40 AND 49) THEN 4
WHEN ((DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) BETWEEN 50 AND 59) THEN 5
WHEN ((DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) BETWEEN 60 AND 69) THEN 6
WHEN ((DATE_FORMAT(NOW(), '%Y')-LEFT(i.`BIRTHDAY`,4)) BETWEEN 70 AND 150) THEN 7
ELSE
CASE
WHEN (DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) < 18 THEN 1
WHEN ((DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) BETWEEN 18 AND 29) THEN 2
WHEN ((DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) BETWEEN 30 AND 39) THEN 3
WHEN ((DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) BETWEEN 40 AND 49) THEN 4
WHEN ((DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) BETWEEN 50 AND 59) THEN 5
WHEN ((DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) BETWEEN 60 AND 69) THEN 6
WHEN ((DATE_FORMAT(NOW(), '%Y')-SUBSTRING(u.`IDCARD`,7,4)) BETWEEN 70 AND 150) THEN 7
ELSE
CASE
WHEN i.`AGE` < 18 THEN 1
WHEN ( i.`AGE` BETWEEN 18 AND 29) THEN 2
WHEN ( i.`AGE` BETWEEN 30 AND 39) THEN 3
WHEN ( i.`AGE` BETWEEN 40 AND 49) THEN 4
WHEN ( i.`AGE` BETWEEN 50 AND 59) THEN 5
WHEN ( i.`AGE` BETWEEN 60 AND 69) THEN 6
WHEN ( i.`AGE` BETWEEN 70 AND 150) THEN 7
ELSE 3
END
END
END ageRange,
COUNT(1) AS quantity
FROM
`t_user_gdata` u,`t_user_organization` o,`t_user_info` i
WHERE u.`organizationId`=o.`organizationId`
AND u.`ID`=i.`USERID`
-- AND DATE_FORMAT(u.`CREATE_TIME`,'%Y-%m-%d') = DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL -1 DAY)
GROUP BY u.`organizationId`,o.`organizationPath`,ageRange;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
/*删除历史数据*/
DELETE
FROM
t_user_gdata_statistics_total_count_by_age_range ;
#set iCount = 0;
/*对历史数据进行重新统计*/
OPEN cur ;
REPEAT
FETCH cur INTO vOrgId,
vOrgPath,
vTime,
vAgeRange,
vQuantity;
IF done = 0 THEN
SET vId = REPLACE(UUID(), '-', '') ;
INSERT INTO t_user_gdata_statistics_total_count_by_age_range (
id,
ageRange,
quantity,
organizationId,
organizationPath
) VALUE (
vId,
vAgeRange,
vQuantity,
vOrgId,
vOrgPath
) ;
#set iCount = iCount + 1;
#select CONCAT('循环次数:', iCount) ;
END IF;
UNTIL done
END REPEAT ;
CLOSE cur ;
END$$

DELIMITER ;

如果有两张表,可以写两个存储过程在删除统计表数据的时候任选一张::如下----->

DELIMITER $$

USE `bbbb`$$

DROP PROCEDURE IF EXISTS `A0_history_user_gdata_statistics_measurement_by_jiankangting`$$

CREATE DEFINER=`root`@`%` PROCEDURE `A0_history_user_gdata_statistics_measurement_by_jiankangting`()
BEGIN
DECLARE vId VARCHAR (50) ;
DECLARE vTimes VARCHAR (10) ;
DECLARE vNormalQuantity INT(11) ;
DECLARE vBussinessTypeId INT(11);
DECLARE vOrganizationId INT (11);
DECLARE vOrganizationPath TEXT ;
-- 遍历数据结束标志
DECLARE done INT DEFAULT 0;
DECLARE iCount INT ;
DECLARE cur CURSOR FOR
-- 查询健康亭每个测量项的总正常次数
SELECT
DATE_FORMAT (t.testDate,'%Y-%m-%d') times,
COUNT(*) normalQuantity,
t.`bussinessTypeId` bussinessTypeId,
t.`organizationId` organizationId,
t.organizationPath organizationPath
FROM
`t_user_measurement_ref` t
WHERE
t.`userGroupId`
IN (3, 4, 16, 19, 25, 36, 40, 48)

AND t.`source` = 17 GROUP BY t.`bussinessTypeId`, t.`organizationId`, t.`organizationPath`;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
/*删除历史数据*/
DELETE
FROM
t_user_measurement_total_by_jiankangting ;
#set iCount = 0;
/*对历史数据进行重新统计*/
OPEN cur ;
REPEAT
FETCH cur INTO
vTimes,
vNormalQuantity,
vBussinessTypeId,
vOrganizationId,
vOrganizationPath;

IF done = 0 THEN
SET vId = REPLACE(UUID(), '-', '') ;
INSERT INTO t_user_measurement_total_by_jiankangting (
id,
times,
normalQuantity,
bussinessTypeId,
organizationId,
organizationPath
) VALUE (
vId,
vTimes,
vNormalQuantity ,
vBussinessTypeId,
vOrganizationId,
vOrganizationPath
) ;
#set iCount = iCount + 1;
#select CONCAT('循环次数:', iCount) ;
END IF;
UNTIL done
END REPEAT ;
CLOSE cur ;
END$$

DELIMITER ;

DELIMITER $$

USE `bbbb`$$

DROP PROCEDURE IF EXISTS `A0_history_user_gdata_statistics_measuringabnormal_by_beikangyi`$$

CREATE DEFINER=`root`@`%` PROCEDURE `A0_history_user_gdata_statistics_measuringabnormal_by_beikangyi`()
BEGIN
DECLARE vId VARCHAR (50) ;
DECLARE vTimes VARCHAR (10) ;
DECLARE vNormalQuantity INT(11) ;
DECLARE vBussinessTypeId INT(11);
DECLARE vOrganizationId INT (11);
DECLARE vOrganizationPath TEXT ;
-- 遍历数据结束标志
DECLARE done INT DEFAULT 0;
DECLARE iCount INT ;
DECLARE cur CURSOR FOR
-- 先查询到前一天倍康仪每个测量项的总异常次数
SELECT
DATE_FORMAT (t.testDate,'%Y-%m-%d') times,
COUNT(*) abnormalQuantity,
t.`bussinessTypeId`,
t.`organizationId`,
t.`organizationPath`
FROM
`t_user_measuringabnormal_ref` t
WHERE
-- DATE_FORMAT(t.`testDate`, '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 DAY),'%Y-%m-%d') AND
t.`source` = 9
GROUP BY t.`bussinessTypeId`, t.`organizationId`, t.`organizationPath`;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
/*之前数据已经删除过历史数据*/
-- DELETE
-- FROM
-- t_user_measurement_total_by_beikangyi ;
#set iCount = 0;
/*对历史数据进行重新统计*/
OPEN cur ;
REPEAT
FETCH cur INTO
vTimes,
vNormalQuantity,
vBussinessTypeId,
vOrganizationId,
vOrganizationPath;

IF done = 0 THEN
SET vId = REPLACE(UUID(), '-', '') ;
INSERT INTO t_user_measurement_total_by_beikangyi (
id,
times,
abnormalQuantity,
bussinessTypeId,
organizationId,
organizationPath
) VALUE (
vId,
vTimes,
vNormalQuantity ,
vBussinessTypeId,
vOrganizationId,
vOrganizationPath
) ;
#set iCount = iCount + 1;
#select CONCAT('循环次数:', iCount) ;
END IF;
UNTIL done
END REPEAT ;
CLOSE cur ;
END$$

DELIMITER ;