我需要计算来自同一个表的不同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
每列都有自己的标准,到目前为止,我有以下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,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
任何想法,我怎样才能实现我的需要？
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