SELECT T.DWMC,
T.ZGJL,
T.ZBJL,
T.ZQJL,
T.XLJL,
T.SKJL,
T.BGJL,
T.BQJL,
T.BGJL2,
T.PXJL,
T.CCJL,
(ZBJL + ZQJL + XLJL + SKJL + BGJL + BQJL + BGJL2 + PXJL + CCJL) AS HZ
FROM (SELECT CODE_MEAN AS DWMC,
(SELECT CASE WHEN B.ZGZT IN("0101", "0102", "0103", "0104", "0205", "0206", "0105") THEN COUNT(*) AS zghl,
CASE WHEN B.ZGZT IN("0102") THEN COUNT(*) AS ZBJL,
CASE WHEN B.ZGZT IN("0103") THEN COUNT(*) AS ZQJL,
CASE WHEN B.ZGZT IN("0205") THEN COUNT(*) AS WXJL,
CASE WHEN B.ZGZT IN("0206") THEN COUNT(*) AS SKJL,
CASE WHEN B.ZGZT IN("0101") THEN COUNT(*) AS BGJL,
CASE WHEN B.ZGZT IN("0104") THEN COUNT(*) AS BQJL,
CASE WHEN B.ZGZT IN("0105") THEN COUNT(*) AS BQJL2,
CASE WHEN B.ZGZT IN("0201") THEN COUNT(*) AS PXJL,
CASE WHEN B.ZGZT IN("0202") THEN COUNT(*) AS CCJL
FROM TABLEONEB, TABLETWO A
WHERE A.LEADER_CODE = B.LEADER_CODE
AND A.TABLETWO99 < "5"
AND B.SZDWDM = CODE
AND TO_CHAR(B.RQ, "yyyy-MM-dd") =
TO_CHAR(SYSDATE, "yyyy-MM-dd")
) A
FROM ZB02_JGMC
WHERE CODE LIKE "500107%") T