SQL中CASE WHEN的每个条件是否可以关联不同的表？

可以实现，你提到的需求本质是两个统计指标的关联表范围不同，要避免不需要的关联过滤掉统计范围的数据，有两种常见实现方案：

方案1：使用LEFT JOIN替代INNER JOIN（适配当前场景） #

你现有SQL的核心问题是INNER JOIN invoices会直接过滤掉所有没有关联发票的预约，导致统计initial_Non_Billable（仅需统计符合条件的预约数量，不需要发票数据）时漏算了无发票的预约。
仅需将发票表的关联改为左连接，无需修改CASE WHEN逻辑，就能实现两个指标的统计范围符合要求：

select
    COALESCE(Clinic,'total') as Clinic,
    sum(Non_Billable) as Non_Billable,
    sum(initial_Non_Billable) as initial_Non_Billable,
    sum(Non_Billable)/NULLIF(sum(initial_Non_Billable),0) as Non_Billable_initial_revenue
FROM (
    select
        businesses.label as Clinic,
    sum(CASE
        WHEN appointment_types.category IN ('Others','Non-Billable')
            and appointment_types.name like '%initial%'
        then invoices.net_amount ELSE 0 END)
        as Non_Billable,
    count(CASE
        WHEN appointment_types.category IN ('Others','Non-Billable')
            and appointment_types.name like '%initial%'
        then appointment_types.name ELSE null END)
        as initial_Non_Billable
    
    FROM
        individual_appointments
        INNER join appointment_types on appointment_types.id = individual_appointments.appointment_type_id
        -- 改为左连接，没有发票的预约也会保留，不影响预约计数
        LEFT join invoices on invoices.appointment_id = individual_appointments.id
        inner join businesses on businesses.id = individual_appointments.business_id
    group by
        businesses.label,
        appointment_types.name,
        appointment_types.category,
        invoices.net_amount
)x
group by
    ROLLUP(Clinic);

方案2：分指标子查询后关联 #

如果两个指标的统计逻辑差异极大，也可以分别写独立子查询统计对应指标，再按Clinic字段关联合并，这种方式逻辑完全隔离，不会出现关联表互相干扰的问题：

select
    COALESCE(t1.Clinic, t2.Clinic, 'total') as Clinic,
    COALESCE(sum(t1.Non_Billable), 0) as Non_Billable,
    COALESCE(sum(t2.initial_Non_Billable), 0) as initial_Non_Billable,
    COALESCE(sum(t1.Non_Billable),0)/NULLIF(COALESCE(sum(t2.initial_Non_Billable),0),0) as Non_Billable_initial_revenue
FROM
-- 子查询1：统计需要关联发票的Non_Billable
(
    select
        businesses.label as Clinic,
        sum(invoices.net_amount) as Non_Billable
    FROM individual_appointments
    INNER join appointment_types on appointment_types.id = individual_appointments.appointment_type_id
    INNER join invoices on invoices.appointment_id = individual_appointments.id
    INNER join businesses on businesses.id = individual_appointments.business_id
    WHERE appointment_types.category IN ('Others','Non-Billable')
      and appointment_types.name like '%initial%'
    group by businesses.label
) t1
-- 全关联两个子查询，避免单侧无数据时被过滤
FULL OUTER JOIN
-- 子查询2：统计不需要关联发票的initial_Non_Billable
(
    select
        businesses.label as Clinic,
        count(*) as initial_Non_Billable
    FROM individual_appointments
    INNER join appointment_types on appointment_types.id = individual_appointments.appointment_type_id
    INNER join businesses on businesses.id = individual_appointments.business_id
    WHERE appointment_types.category IN ('Others','Non-Billable')
      and appointment_types.name like '%initial%'
    group by businesses.label
) t2 on t1.Clinic = t2.Clinic
group by ROLLUP(COALESCE(t1.Clinic, t2.Clinic));

内容的提问来源于stack exchange，提问作者ryan