相关oracle中SQL的查询

SELECT DISTINCT * FROM TB_VISIT_PLAN TVP
INNER JOIN TB_CUSTOMER_USER tcu on tcu.USER_ID=TVP.CREATOR 
INNER JOIN TB_CUSTOMER tc on tc.CUSTOMER_ID=TCU.CUSTOMER_ID
LEFT JOIN tb_customer_user us on us.customer_id = tc.customer_id and us.service_status = 1
WHERE TVP.CUSTOMER_ID in(
--没填写记录或者记录数没有计划数大
SELECT AA.CUSTOMER_ID FROM (
SELECT COUNT(CUSTOMER_ID) as c,CUSTOMER_ID FROM TB_VISIT_PLAN
 WHERE VISIT_TIME>(
SELECT TO_DATE(to_char(ADD_MONTHS(sysdate,-1),'yyyy-MM-')||(SELECT CASE WHEN PLANENDTIME<K THEN PLANENDTIME ELSE K END
FROM TB_VISIT_PLAN_SETTING ,(SELECT to_number(to_char(LAST_DAY(SYSDATE),'dd')) as K FROM dual)),'yyyy-MM-dd') as lastTime  from dual
) AND VISIT_TIME<(
SELECT  TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-')||(SELECT CASE WHEN PLANENDTIME<K THEN PLANENDTIME ELSE K END
FROM TB_VISIT_PLAN_SETTING ,(SELECT to_number(to_char(LAST_DAY(SYSDATE),'dd')) as K FROM dual)), 'yyyy-MM-dd')  FROM dual
)
 GROUP BY CUSTOMER_ID) AA
 INNER   JOIN 
(SELECT COUNT(CUSTOMER_ID) as m,CUSTOMER_ID FROM TB_VISIT_RECORD 
WHERE VISIT_TIME >(
SELECT TO_DATE(to_char(ADD_MONTHS(sysdate,-1),'yyyy-MM-')||(SELECT CASE WHEN PLANENDTIME<K THEN PLANENDTIME ELSE K END
FROM TB_VISIT_PLAN_SETTING ,(SELECT to_number(to_char(LAST_DAY(SYSDATE),'dd')) as K FROM dual)),'yyyy-MM-dd') as lastTime  from dual
) AND VISIT_TIME<(
SELECT  TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-')||(SELECT CASE WHEN PLANENDTIME<K THEN PLANENDTIME ELSE K END
FROM TB_VISIT_PLAN_SETTING ,(SELECT to_number(to_char(LAST_DAY(SYSDATE),'dd')) as K FROM dual)), 'yyyy-MM-dd')  FROM dual
)
 GROUP BY CUSTOMER_ID) BB 
ON AA.c>BB.M AND AA.CUSTOMER_ID=BB.CUSTOMER_ID
);

精简版本：

SELECT SS.STAFF_ID,SS.USER_NAME,SS.EMAIL,CUS.CUSTOMER_ID,CUS.CUSTOMER_NAME
FROM TB_CUSTOMER CUS
INNER JOIN TB_CUSTOMER_USER CU ON CU.CUSTOMER_ID = CUS.CUSTOMER_ID AND CU.SERVICE_STATUS = 1
INNER JOIN TB_SYS_STAFF SS ON SS.STAFF_ID = CU.USER_ID
INNER JOIN (SELECT C.CUSTOMER_ID
                  ,COUNT(VP.VISIT_PLAN_ID) PLAN
                  ,COUNT(VR.VISIT_RECORD_ID) RECORD
            FROM TB_CUSTOMER C
            LEFT JOIN TB_VISIT_PLAN VP ON VP.CUSTOMER_ID = C.CUSTOMER_ID AND VP.ISDELETE = 0
            LEFT JOIN TB_VISIT_RECORD VR ON VR.CUSTOMER_ID = C.CUSTOMER_ID AND VR.ISDELETE = 0
                  AND TRUNC(VR.VISIT_TIME,'MM') = TRUNC(VP.VISIT_TIME,'MM')
            WHERE TRUNC(VP.VISIT_TIME,'MM') = TRUNC(SYSDATE,'MM')
            GROUP BY C.CUSTOMER_ID
           ) V ON V.CUSTOMER_ID = CUS.CUSTOMER_ID
WHERE CUS.CUSTOMER_STATUS = 1
  AND V.PLAN > 0
  AND V.RECORD < V.PLAN