高级SQL之 CASE WHEN

SELECT
    `o`.`id` AS `organizationId`,
    `o`.`organization_code` AS `organizationCode`,
    `o`.`organization_name` AS `organizationName`,

    -- 去重并统计行数(p.id != null)
    count( DISTINCT p.id ) AS patientNum,

    -- 统计`p.in_group`=-1的行数
    sum( CASE WHEN p.in_group = -1 THEN 1 ELSE 0 END ) AS exceptNum,

    -- 统计`p.is_valid`=1的行数
    sum( CASE WHEN p.is_valid = 1 THEN 1 ELSE 0 END ) AS validNum,

    -- 统计`p.atrial_fibrillation`为1或3的行数
    sum( CASE WHEN p.atrial_fibrillation IN (1, 3) THEN 1 ELSE 0 END ) AS atrialConfirmNum,

    -- 统计`p.atrial_fibrillation`=4的行数
    sum( CASE WHEN p.atrial_fibrillation = 4 THEN 1 ELSE 0 END ) AS atrialPastNum,

    -- 统计`p.atrial_fibrillation`=2的行数
    sum( CASE WHEN p.atrial_fibrillation = 2 THEN 1 ELSE 0 END ) AS atrialExceptNum,

    -- 统计`p.filter_code`不为空的行数
    sum( CASE WHEN p.filter_code IS NOT NULL AND p.filter_code <> "" THEN 1 ELSE 0 END ) AS inScreenNum,

    -- 统计`p.group_id`值不为null的行数
    count( p.group_id ) AS inGroupNum
FROM
    `organization` AS `o`
    LEFT JOIN `patient` AS `p` ON `o`.`id` = `p`.`organization_id`
WHERE
    `o`.`is_del` = 0
GROUP BY
    `o`.`id`
ORDER BY
    `patientNum` DESC,
    `exceptNum` DESC,
    `o`.`create_time` DESC
    LIMIT 15 OFFSET 0