Oracle：如何做多个计数用不同的where子句最好的方法？ [英] Oracle: How to do multiple counts with different where clauses the best way?

问题描述

我需要计算来自同一个表的具有不同where子句的行。以下是我需要的输出

I have requirement to count rows with different where clauses from the same table. The following is the required output from me

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，但它已经看起来很丑陋，我需要

Each column has its own criteria, so far I have the following SQL but it already looks ugly and doesn't exactly return what I need

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

任何想法，我如何实现我需要的？

Any ideas, how can I achieve what I need?

推荐答案

  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

这篇关于Oracle：如何做多个计数用不同的where子句最好的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！