问题描述

首页的待复习数量与复习页的数量不一致

问题排查

0. 以openid为例

o4nOp5P7IQTHWDp_RGjEyHvTKZFg

1. 首页的数量

接口

https://43008f543x.oicp.vip/3.1/statistic/review

service

CardService.getNeedReviewCardCount()

sql语句

SELECT
	COUNT(
	DISTINCT ( card.id )) 
FROM
	card
	INNER JOIN USER ON USER.openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' 
WHERE
	card.openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' 
	AND card.DELETED = 0 
	AND DATEDIFF( NOW(), card.CREATE_DATE ) != 0 
	AND DATEDIFF( NOW(), next_need_review_time ) >= 0 
	AND card.valid_practice_seq < 8 
	AND card.dict_code = USER.current_dict_code

结果

2. 复习页的数量来源于

接口

https://43008f543x.oicp.vip/3.1/reviewcards/datelist

service

CardService.getReviewCardDateList()

sql语句:

SELECT
   DATE_FORMAT( CREATE_DATE, '%Y%m%d' ) AS groupDate,
   count(*) AS 'cardNum',
   max( id ) AS 'firstCardID' 
FROM
   card 
WHERE
   openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' 
   AND DATEDIFF(
   	CREATE_DATE,
   NOW()) != 0 
   AND next_need_review_time < NOW() 
   AND valid_practice_seq < 8 
   AND dict_code = ( SELECT current_dict_code FROM USER WHERE openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' ) 
   AND DELETED = 0 
GROUP BY
   DATE_FORMAT( CREATE_DATE, '%Y%m%d' ) 
ORDER BY
   groupDate DESC

结果

解决方案

两边的计数方式不一致导致的

SELECT
	DATE_FORMAT( CREATE_DATE, '%Y%m%d' ) AS groupDate,
	count(*) AS 'cardNum',
	max( id ) AS 'firstCardID' 
FROM
	card 
WHERE
	openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' 
	AND DATEDIFF( NOW(), card.CREATE_DATE ) != 0 
	AND DELETED = 0 
	AND next_need_review_time < NOW()  
	AND valid_practice_seq < 8 
	AND dict_code = ( SELECT current_dict_code FROM USER WHERE openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' ) 
GROUP BY
	DATE_FORMAT( CREATE_DATE, '%Y%m%d' ) 
ORDER BY
	groupDate DESC

SELECT
	COUNT(
	DISTINCT ( card.id )) 
FROM
	card
	INNER JOIN USER ON USER.openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' 
WHERE
	card.openid = 'o4nOp5P7IQTHWDp_RGjEyHvTKZFg' 
	AND DATEDIFF( NOW(), card.CREATE_DATE ) != 0 
	AND card.DELETED = 0 
	AND next_need_review_time < NOW()  
	AND card.valid_practice_seq < 8 
	AND card.dict_code = USER.current_dict_code