Oracle︰ 如何做多个计数与不同在哪里条款的最佳方式吗?

标签: Oracle
发布时间: 2017/4/15 18:28:27
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有不同的地方的行进行计数的要求条款从相同的表。 以下是从我所需的输出

Bu   #A   #B  #C  #D #E #F #G  #H  #J  #K  #L   #M  #N
GB01 267  284 84  45 35 32 458 801 111 899 892  56  99
NL01 132  844 65  28 26 12 627 321 56  681 1062 127 128

每个列都有其自身的标准,迄今以下 SQL 但它已经看起来很丑并不确切地返回我的需要

SELECT *  FROM (
  SELECT
    c_unit_code,
    COUNT(*) AS ADVICE_EXPORT,
    0 AS CONFIRMATION_EXPORT,
    0 AS ISSUANCE_STANDBY
  FROM EXIMTRX.EPLC_MASTER
  WHERE (CLS_FLG NOT LIKE 'YES' OR CLS_FLG IS NULL) AND (
    form_of_lc LIKE 'IRREVOCABLE' OR
    form_of_lc LIKE 'REVOCABLE' OR
    form_of_lc LIKE 'IRREVOCABLE TRANSFERABLE' OR
    form_of_lc LIKE 'REVOCABLE TRANSFERABLE') AND our_eng LIKE 'ADVICE'
    GROUP BY c_unit_code
UNION
  SELECT
    c_unit_code,
    0 AS ADVICE_EXPORT,
    COUNT(*) AS CONFIRMATION_EXPORT,
    0 AS ISSUANCE_STANDBY
  FROM EXIMTRX.EPLC_MASTER
  WHERE (CLS_FLG NOT LIKE 'YES' OR CLS_FLG IS NULL) AND (
    form_of_lc LIKE 'IRREVOCABLE' OR
    form_of_lc LIKE 'REVOCABLE' OR
    form_of_lc LIKE 'IRREVOCABLE TRANSFERABLE' OR
    form_of_lc LIKE 'REVOCABLE TRANSFERABLE') AND our_eng LIKE 'CONFIRMATION'
    GROUP BY c_unit_code
UNION
  SELECT 
    c_unit_code,
    0 AS ADVICE_EXPORT,
    0 AS CONFIRMATION_EXPORT,
    COUNT(*) AS ISSUANCE_STANDBY
  FROM EXIMTRX.EPLC_MASTER
  WHERE (CLS_FLG NOT LIKE 'YES' OR CLS_FLG IS NULL) AND (
    form_of_lc LIKE 'IRREVOCABLE STANDBY' OR
    form_of_lc LIKE 'REVOCABLE STANDBY' OR
    form_of_lc LIKE 'IRREVOC TRANS STANDBY')
    GROUP BY c_unit_code
);

这是它的返回

GB01    0   0   17
GB01    0   39  0
GB01    80  0   0
NL01    0   0   32
NL01    0   159 0
NL01    341 0   0

任何的想法,如何能实现我的需要?

解决方法 1:

  SELECT
    c_unit_code,
    COUNT(case when YOUR_CONDITIONS_FOR_ADVICE_EXPORT then 1 end) AS ADVICE_EXPORT,
    COUNT(case when YOUR_CONDITIONS_FOR_CONFIRMATION_EXPORT then 1 end) AS CONFIRMATION_EXPORT,
    COUNT(case when YOUR_CONDITIONS_FOR_ISSUANCE_STANDBY then 1 end) AS ISSUANCE_STANDBY
  FROM EXIMTRX.EPLC_MASTER
  GROUP BY c_unit_code
赞助商