时间戳

首页 > 解决方案 > 显示 case 表达式中的空白行

sql - 显示 case 表达式中的空白行

问题描述

我正在编写一个 sql 报告，对我提出的要求之一是包含 CASE 表达式的两个选项的行。

该报告将显示数据库中所有客户与 3 个选定客户中的每一个之间的“成本计算”和“非成本计算”交易计数。

请参阅所需的示例布局。

CUSTOMER_ID | CUSTOMER_NAME | COSTING     | CUST_1 | CUST_2 | CUST_3
12          | Red Corp      | Non-costing | 1245   | 734    | 84
12          | Red Corp      | Costing     | 376    | 23     | 2
17          | Blue Corp     | Non-costing | 4538   | 36     | 3256
17          | Blue Corp     | Costing     | 0      | 0      | 0
...

我尝试将 COSTING 列放在案例语句中。但是，由于它是空白的，因此我无法在输出中显示示例“Blue Corp”“成本核算”行。

select  CUSTOMER_ID
        ,CUSTOMER_NAME
        ,case 
            when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null then 'Non-costing'
            when PURCHASE_VALUE != 0 or PURCHASE_VALUE is not null then 'Costing'
        end AS COSTING
,count(CASE WHEN CUSTOMER_ID = 11 then 1 ELSE NULL END) as "CUST_1"
,count(CASE WHEN CUSTOMER_ID = 22 then 1 ELSE NULL END) as "CUST_2"
,count(CASE WHEN CUSTOMER_ID = 33 then 1 ELSE NULL END) as "CUST_3"
from schema.CUSTOMER_TRANSACTIONS
group by case 
            when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null then 'Non-costing'
            when PURCHASE_VALUE != 0 or PURCHASE_VALUE is not null then 'Costing'
        end 
        ,CUSTOMER_ID
        ,CUSTOMER_NAME

我目前与示例相同，但缺少 0 计数行。请在下面查看我目前得到的输出。

CUSTOMER_ID | CUSTOMER_NAME | COSTING     | CUST_1 | CUST_2 | CUST_3
12          | Red Corp      | Non-costing | 1245   | 734    | 84
12          | Red Corp      | Costing     | 376    | 23     | 2
17          | Blue Corp     | Non-costing | 4538   | 36     | 3256
...

标签： sqloraclecase

解决方案

使用 across join生成行并使用 aleft join添加列：

select c.CUSTOMER_ID, c.CUSTOMER_NAME, t.costing,
       sum(CASE WHEN CUSTOMER_ID = 11 then 1 ELSE 0 END) as CUST_1,
       sum(CASE WHEN CUSTOMER_ID = 22 then 1 ELSE 0 END) as CUST_2,
       sum(CASE WHEN CUSTOMER_ID = 33 then 1 ELSE 0 END) as CUST_3
from (select distinct ct.customer_id, ct.customer_name
      from schema.CUSTOMER_TRANSACTIONS ct
     ) c cross join
     (select 'Non-costing' as costing from dual union all
      select 'Costing' from dual
     ) t left join
     schema.customer_transactions ct
     on c.customer_id = ct.customer_id,
        t.costing = (case when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null
                         then 'Non-costing'
                         else 'Costing'
                     end)
group by c.CUSTOMER_ID, c.CUSTOMER_NAME, t.type;

推荐阅读