复杂sql case when distinct

select * from (SELECT
        a.resident_id residentId,
        b.name,
        b.phone_number phoneNumber,
        b.health_code healthCode,
        b.responsible_doctor_id,
        case when datediff(now(),d.measureTime) = 0 then '今日'
        when datediff(now(),d.measureTime) = 1 then '昨日'
        when datediff(now(),d.measureTime) = 2 then '三天内'
        when datediff(now(),d.measureTime) > 2 and  7 >= datediff(now(),d.measureTime) then '一周内'
        when datediff(now(),d.measureTime) > 7 and  30 >= datediff(now(),d.measureTime) then '一月内'
        when datediff(now(),d.measureTime) > 30 then '一月以上'
        else '-' end as measureTime,d.measureTime measureDate,
        c.type_name typeName,
        d.sbpValue,
        d.dbpValue,
        d.measureResult,
        c.device_type deviceType,
        c.id deviceId,
        c.device_code deviceCode,
        d.measureCount,
        count( distinct g.execute_id ) completeMonitorCount, 
        count( distinct e.execute_id ) monitorCount,
        count( distinct f.abnormal_id ) abnormalCount,
        a.bind_time bindTime
        FROM
        device_operate a
        INNER JOIN resident_base b
        ON a.resident_id = b.resident_id
        AND b.is_deleted = 0
        INNER JOIN device_manager c
        ON a.device_id = c.id
        and c.is_deleted = 0
        AND (a.unbind_time IS NULL OR a.bind_time > a.unbind_time)
        left JOIN (
        SELECT
        device_id,
        device_bp_record_id recordId,count(1)measureCount,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
        SUBSTRING_INDEX( GROUP_CONCAT( sbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `sbpValue`,
        SUBSTRING_INDEX( GROUP_CONCAT( dbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `dbpValue`,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
        FROM
        device_bp_record
        GROUP BY
        device_id UNION
        SELECT
        device_id,
        device_bg_record_id recordId,count(1)measureCount,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
        SUBSTRING_INDEX( GROUP_CONCAT( bg_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `bgValue`,
        NULL,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
        FROM
        device_bg_record
        GROUP BY
        device_id
        ) d ON a.device_id = d.device_id
        LEFT JOIN cd_recipe_monitor_execute e
        ON a.resident_id = e.resident_id
        and e.is_deleted = 0
        LEFT JOIN cd_abnormal f
        on f.device_id = c.id
        and f.abnormal_source_code = 5
        and f.abnormal_status = 1
        and f.is_deleted = 0
        LEFT JOIN  cd_recipe_monitor_execute g
        ON a.resident_id = g.resident_id
        and g.is_deleted = 0
        and g.is_executed = 1
        GROUP BY
        c.id) rs;

select * from (SELECT
        a.resident_id residentId,
        b.name,
        b.phone_number phoneNumber,
        b.health_code healthCode,
        b.responsible_doctor_id,
        case when datediff(now(),d.measureTime) = 0 then '今日'
        when datediff(now(),d.measureTime) = 1 then '昨日'
        when datediff(now(),d.measureTime) = 2 then '三天内'
        when datediff(now(),d.measureTime) > 2 and  7 >= datediff(now(),d.measureTime) then '一周内'
        when datediff(now(),d.measureTime) > 7 and  30 >= datediff(now(),d.measureTime) then '一月内'
        when datediff(now(),d.measureTime) > 30 then '一月以上'
        else '-' end as measureTime,d.measureTime measureDate,
        c.type_name typeName,
        d.sbpValue,
        d.dbpValue,
        d.measureResult,
        c.device_type deviceType,
        c.id deviceId,
        c.device_code deviceCode,
        d.measureCount,
                (SELECT count(DISTINCT execute_id) from cd_recipe_monitor_execute me  
                where a.resident_id = me.resident_id and me.is_deleted = 0 
                and me.is_executed = 1 and 2 > DATEDIFF(me.plan_date,NOW())
                and case when c.device_type=1 then me.monitor_type in(2,3) 
                when c.device_type=2 then me.monitor_type =1 end) completeMonitorCount,
                (SELECT count(DISTINCT execute_id) from cd_recipe_monitor_execute me  
                where a.resident_id = me.resident_id and me.is_deleted = 0 
                and 2 > DATEDIFF(me.plan_date,NOW()) and 
                case when c.device_type=1 then me.monitor_type in(2,3) 
                when c.device_type=2 then me.monitor_type =1 end) monitorCount,
        count(f.abnormal_id) abnormalCount,
        a.bind_time bindTime
        FROM
        device_operate a
        INNER JOIN resident_base b
        ON a.resident_id = b.resident_id
        AND b.is_deleted = 0
        INNER JOIN device_manager c
        ON a.device_id = c.id
        and c.is_deleted = 0
        AND (a.unbind_time IS NULL OR a.bind_time > a.unbind_time)
        left JOIN (
        SELECT
        device_id,
        device_bp_record_id recordId,count(1)measureCount,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
        SUBSTRING_INDEX( GROUP_CONCAT( sbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `sbpValue`,
        SUBSTRING_INDEX( GROUP_CONCAT( dbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `dbpValue`,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
        FROM
        device_bp_record
        GROUP BY
        device_id UNION
        SELECT
        device_id,
        device_bg_record_id recordId,count(1)measureCount,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
        SUBSTRING_INDEX( GROUP_CONCAT( bg_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `bgValue`,
        NULL,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
        FROM
        device_bg_record
        GROUP BY
        device_id
        ) d ON a.device_id = d.device_id
        LEFT JOIN cd_abnormal f
        on f.device_id = c.id
        and f.abnormal_source_code = 5
        and f.abnormal_status = 1
        and f.is_deleted = 0
        GROUP BY
        c.id) rs
        where 1 = 1

select * from (SELECT
a.resident_id residentId,
b.name,
b.phone_number phoneNumber,
b.health_code healthCode,
b.responsible_doctor_id,
case when datediff(now(),d.measureTime) = 0 then '今日'
when datediff(now(),d.measureTime) = 1 then '昨日'
when datediff(now(),d.measureTime) = 2 then '三天内'
when datediff(now(),d.measureTime) > 2 and 7 >= datediff(now(),d.measureTime) then '一周内'
when datediff(now(),d.measureTime) > 7 and 30 >= datediff(now(),d.measureTime) then '一月内'
when datediff(now(),d.measureTime) > 30 then '一月以上'
else '-' end as measureTime,d.measureTime measureDate,
c.type_name typeName,
d.sbpValue,
d.dbpValue,
d.measureResult,
c.device_type deviceType,
c.id deviceId,
c.device_code deviceCode,
d.measureCount,

IF(c.device_type = 1,count( distinct i.execute_id),count(distinct h.execute_id)) completeMonitorCount,
IF(c.device_type = 1,count( distinct g.execute_id),count(distinct f.execute_id)) monitorCount,
count( distinct e.abnormal_id ) abnormalCount,
a.bind_time bindTime
FROM
device_operate a
INNER JOIN resident_base b
ON a.resident_id = b.resident_id
AND b.is_deleted = 0
INNER JOIN device_manager c
ON a.device_id = c.id
and c.is_deleted = 0
AND (a.unbind_time IS NULL OR a.bind_time > a.unbind_time)
left JOIN (
SELECT
device_id,
device_bp_record_id recordId,count(1)measureCount,
SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
SUBSTRING_INDEX( GROUP_CONCAT( sbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `sbpValue`,
SUBSTRING_INDEX( GROUP_CONCAT( dbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `dbpValue`,
SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
FROM
device_bp_record
GROUP BY
device_id UNION
SELECT
device_id,
device_bg_record_id recordId,count(1)measureCount,
SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
SUBSTRING_INDEX( GROUP_CONCAT( bg_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `bgValue`,
NULL,
SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
FROM
device_bg_record
GROUP BY
device_id
) d ON a.device_id = d.device_id
LEFT JOIN cd_abnormal e
on e.device_id = c.id
and e.abnormal_source_code = 5
and e.abnormal_status = 1
and e.is_deleted = 0
LEFT JOIN cd_recipe_monitor_execute f
ON a.resident_id = f.resident_id
and f.is_deleted = 0
and f.monitor_type = 1
LEFT JOIN cd_recipe_monitor_execute g
ON a.resident_id = g.resident_id
and g.is_deleted = 0
and g.monitor_type in(2,3)
LEFT JOIN cd_recipe_monitor_execute h
ON a.resident_id = h.resident_id
and h.is_deleted = 0
and h.is_executed = 1
and h.monitor_type = 1
LEFT JOIN cd_recipe_monitor_execute i
ON a.resident_id = i.resident_id
and i.is_deleted = 0
and i.is_executed = 1
and i.monitor_type in(2,3)
GROUP BY
c.id) rs
where 1 = 1

    
                select * from (SELECT
        a.resident_id residentId,
        b.name,
        b.phone_number phoneNumber,
        b.health_code healthCode,
        b.responsible_doctor_id,
        case when datediff(now(),d.measureTime) = 0 then '今日'
        when datediff(now(),d.measureTime) = 1 then '昨日'
        when datediff(now(),d.measureTime) = 2 then '三天内'
        when datediff(now(),d.measureTime) > 2 and  7 >= datediff(now(),d.measureTime) then '一周内'
        when datediff(now(),d.measureTime) > 7 and  30 >= datediff(now(),d.measureTime) then '一月内'
        when datediff(now(),d.measureTime) > 30 then '一月以上'
        else '-' end as measureTime,d.measureTime measureDate,
        c.type_name typeName,
        d.sbpValue,
        d.dbpValue,
        d.measureResult,
        c.device_type deviceType,
        c.id deviceId,
        c.device_code deviceCode,
        d.measureCount,
        count( CASE WHEN e.is_executed = 1 THEN 1 ELSE NULL END ) completeMonitorCount,
        count( CASE WHEN (c.device_type=1 and e.monitor_type in(2,3))||(c.device_type=2 and e.monitor_type =1) THEN 1 ELSE NULL END ) monitorCount,
        count( distinct f.abnormal_id ) abnormalCount,
        a.bind_time bindTime
        FROM
        device_operate a
        INNER JOIN resident_base b
        ON a.resident_id = b.resident_id
        AND b.is_deleted = 0
        INNER JOIN device_manager c
        ON a.device_id = c.id
        and c.is_deleted = 0
        AND (a.unbind_time IS NULL OR a.bind_time > a.unbind_time)
        left JOIN (
        SELECT
        device_id,
        device_bp_record_id recordId,count(1)measureCount,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
        SUBSTRING_INDEX( GROUP_CONCAT( sbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `sbpValue`,
        SUBSTRING_INDEX( GROUP_CONCAT( dbp_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `dbpValue`,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
        FROM
        device_bp_record
        GROUP BY
        device_id UNION
        SELECT
        device_id,
        device_bg_record_id recordId,count(1)measureCount,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_time ORDER BY `measure_time` DESC ), ',', 1 ) AS measureTime,
        SUBSTRING_INDEX( GROUP_CONCAT( bg_value ORDER BY `measure_time` DESC ), ',', 1 ) AS `bgValue`,
        NULL,
        SUBSTRING_INDEX( GROUP_CONCAT( measure_result ORDER BY `measure_time` DESC ), ',', 1 ) AS `measureResult`
        FROM
        device_bg_record
        GROUP BY
        device_id
        ) d ON a.device_id = d.device_id
        LEFT JOIN cd_abnormal f
        on f.device_id = c.id
        and f.abnormal_source_code = 5
        and f.abnormal_status = 1
        and f.is_deleted = 0
        LEFT JOIN cd_recipe_monitor_execute e
        ON a.resident_id = e.resident_id
        and e.is_deleted = 0
        
        GROUP BY
        c.id) rs
        where 1 = 1