时间戳

首页 > 解决方案 > “case when”中的每个条件是否有可能有不同的表？

mysql - “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
        inner 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);

“case when then”中的每个条件是否有可能有不同的表？

例如：对于“Non_Billable”，只需要从 individual_appointment、appointment_types、invoices 和 business 表中获取。

而“initial_Non_Billable”只需要个人约会、约会类型和企业表。

可以这样吗？

如果可能，怎么做？谁能举个例子？

标签： mysqlsql

解决方案

据我所知，我想这里的问题是每个约会可能有很多发票，所以当你加入发票时，你会得到多倍的约会，这也会使你的计数成倍增加。

因此，您并不是真的想加入发票，而是加入发票金额。即加入前聚合。

然后，您只对不计费的预约感兴趣，因此您可以将该标准放入您的ON条款和外部加入中，以便在没有此类预约的情况下获得诊所。

select
  coalesce(b.label, 'total') as clinic,
  coalesce(sum(i.total_net_amount), 0) as initial_non_billable_sum,
  count(at.id) as initial_non_billable_count,
  avg(i.total_net_amount) as initial_non_billable_average
from businesses b
left join individual_appointments ia on ia.business_id = b.id
left join appointment_types at on at.id = ia.appointment_type_id
                              and at.category in ('others', 'non-billable')
                              and at.name like '%initial%'
left join
(
  select appointment_id, sum(net_amount) as total_net_amount
  from invoices
  group by appointment_id
) i on i.appointment_id = ia.id
group by rollup(b.label)
order by b.label nulls last;

推荐阅读