后台数据库查询记录

一、根据日期按天分组查询倒序

//mapper
public List<Date> dateByPatientId(FollowScheme followScheme);

<select id="dateByPatientId" parameterType="com.ruoyi.follow.domain.FollowScheme" resultType="java.util.Date">
        SELECT DATE(create_time) as create_time FROM follow_scheme
        WHERE platform_id = #{platformId} AND patient_id = #{patientId}
        GROUP BY DATE(create_time)
        ORDER BY MAX(create_time) DESC;
    </select>

总结：

• 查询结果为日期集合，java用List<Date>接收，MyBatis配置resultType="java.util.Date"
• 库中的create_time有具体的时分秒，按天分组转化函数DATE(create_time)
• 分组按日期倒序，最新的日期放在最上面ORDER BY MAX(create_time) DESC

二、查询指定日期的记录

<select id="listByPatientId" parameterType="com.ruoyi.follow.domain.FollowScheme" resultMap="FollowSchemeResult">
        <include refid="selectFollowSchemeVo"/>
        where platform_id = #{platformId} AND patient_id = #{patientId} AND DATE(create_time) = #{createTime}
    </select>

总结：

• 传进来的参数是天，条件DATE(create_time) = #{createTime}

三、统计类型数量，默认填充0

SELECT COALESCE
	( MAX( CASE WHEN follow_type = 0 THEN total_quantity END ), 0 ) AS pushCountVisit,
	COALESCE ( MAX( CASE WHEN follow_type = 1 THEN total_quantity END ), 0 ) AS pushCountQuestion,
	COALESCE ( MAX( CASE WHEN follow_type = 2 THEN total_quantity END ), 0 ) AS pushCountEssay,
	COALESCE ( MAX( CASE WHEN follow_type = 3 THEN total_quantity END ), 0 ) AS pushCountCall 
FROM
	( SELECT follow_type, count( follow_type ) AS total_quantity FROM follow_scheme WHERE platform_id = 0 AND patient_id = 3 GROUP BY follow_type ) AS subquery;

四、年龄段统计

WITH age_ranges AS ( SELECT '40 岁以下' AS age_range UNION ALL SELECT '40-49岁' UNION ALL SELECT '50-59岁' UNION ALL SELECT '60-69岁' UNION ALL SELECT '70 岁以上' ) SELECT
ar.age_range AS NAME,
COALESCE ( COUNT( DISTINCT fs.patient_id ), 0 ) AS 
VALUE
	
FROM
	age_ranges ar
	LEFT JOIN lnm_patient_user pu ON (
	CASE
			
			WHEN pu.age < 40 THEN
			'40岁以下' 
			WHEN pu.age BETWEEN 40 
			AND 50 THEN
				'40-49岁' 
				WHEN pu.age BETWEEN 50 
				AND 60 THEN
					'50-59岁' 
					WHEN pu.age BETWEEN 60 
					AND 70 THEN
						'60-69岁' ELSE '70岁以上' 
						END = ar.age_range 
				)
				LEFT JOIN follow_scheme fs ON pu.patient_id = fs.patient_id 
				AND fs.platform_id = 0 
			GROUP BY
				ar.age_range 
			ORDER BY
			ar.age_range;

五、性别统计

WITH age_ranges AS ( SELECT '男' AS age_range UNION ALL SELECT '女' ) SELECT
ar.age_range AS NAME,
COALESCE ( COUNT( DISTINCT pu.patient_id ), 0 ) AS 
VALUE
	
FROM
	age_ranges ar
	LEFT JOIN lnm_patient_user pu ON ( CASE WHEN pu.sex = 0 THEN '男' ELSE '女' END = ar.age_range )
	LEFT JOIN follow_scheme fs ON pu.patient_id = fs.patient_id 
	AND fs.platform_id = 0 

GROUP BY
	ar.age_range 
ORDER BY
	ar.age_range DESC;