SELECT T1.BIRTHEMP,
T1.BIRTHEMPDEP,
(
CASE
WHEN T2.BLESSNUM IS NULL
THEN 0
ELSE T2.BLESSNUM
END) AS BLESSNUM
FROM
(SELECT E.EMP_NAME AS BIRTHEMP,
M.NAME AS BIRTHEMPDEP,
E.USER_ID
FROM M_EMPLOYEE E
LEFT JOIN M_ORGANIZATION M
ON M.ORG_ID =E.DEPT_ID
WHERE TO_CHAR(E.BIRTHDAY,'MM')=TO_CHAR(SYSDATE,'MM')
)T1
LEFT JOIN
(SELECT TO_USER,
COUNT(*) AS blessnum
FROM T_ECM_BIRTH_BLESS
LEFT JOIN M_EMPLOYEE M
ON M.USER_ID=TO_USER
GROUP BY TO_USER
)T2 ON T1.USER_ID=T2.TO_USER
T1.BIRTHEMPDEP,
(
CASE
WHEN T2.BLESSNUM IS NULL
THEN 0
ELSE T2.BLESSNUM
END) AS BLESSNUM
FROM
(SELECT E.EMP_NAME AS BIRTHEMP,
M.NAME AS BIRTHEMPDEP,
E.USER_ID
FROM M_EMPLOYEE E
LEFT JOIN M_ORGANIZATION M
ON M.ORG_ID =E.DEPT_ID
WHERE TO_CHAR(E.BIRTHDAY,'MM')=TO_CHAR(SYSDATE,'MM')
)T1
LEFT JOIN
(SELECT TO_USER,
COUNT(*) AS blessnum
FROM T_ECM_BIRTH_BLESS
LEFT JOIN M_EMPLOYEE M
ON M.USER_ID=TO_USER
GROUP BY TO_USER
)T2 ON T1.USER_ID=T2.TO_USER