我需要对同一表中具有不同 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,
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
有什么想法,我怎样才能实现我所需要的?
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)