Oracle：如何以最佳方式使用不同的 where 子句进行多次计数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15431698/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How to do multiple counts with different where clauses the best way?
提问by rojanu
I have requirement to count rows with different where clauses from the same table. The following is the required output from me
我需要计算同一个表中具有不同 where 子句的行。以下是我所需的输出
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
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
每列都有自己的标准，到目前为止，我有以下 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
);
and this is what it returns
这就是它返回的内容
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?
任何想法，我怎样才能实现我所需要的？
回答by Egor Skriptunoff
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
