直销客户销售明细

                    SELECT TO_CHAR(T.NY) 年月,
                           (SELECT NAME FROM TPROVINCE a WHERE IBM = T.SF) 省份,
                           (SELECT DISTINCT (A.NAME)
                              FROM LBORGANIZATION A, TTDCY B
                             WHERE B.TD = T.TD
                               AND B.ORGID = A.ID) 部门,
                           (SELECT TDMC FROM TTDBM a WHERE ID = T.TD) 团队,
                           T.GRJE 个人金额,
                           T.GRSL 个人数量,
                           Z.TDJE 机构金额,
                           Z.TDSL 机构数量
                      FROM (SELECT FLOOR(E.CLRQ / 100) NY,
                                   D.PROVINCE SF,
                                   C.TD TD,
                                   SUM(A.HTJE) GRJE,
                                   COUNT(*) GRSL
                              FROM THTXX A, TKHXX B, TTDCY C, lbOrganization D, TFP_CPDM E
                             WHERE B.KHLX = 0
                               AND A.KHH = B.ID
                               AND A.KHJL = C.RYXX
                               AND C.ORGID = D.ID
                               AND A.CPID = E.ID
                               AND A.SFDXHT = 0AND E.CLRQ BETWEEN 20191101 AND 20191231
                             GROUP BY FLOOR(E.CLRQ / 100), D.PROVINCE, C.TD) T,
                           (SELECT FLOOR(E.CLRQ / 100) NY,
                                   D.PROVINCE SF,
                                   C.TD TD,
                                   SUM(A.HTJE) TDJE,
                                   COUNT(*) TDSL
                              FROM THTXX A, TKHXX B, TTDCY C, lbOrganization D, TFP_CPDM E
                             WHERE B.KHLX = 1
                               AND A.KHH = B.ID
                               AND A.KHJL = C.RYXX
                               AND C.ORGID = D.ID
                               AND A.CPID = E.ID
                               AND A.SFDXHT = 0AND E.CLRQ BETWEEN 20191101 AND 20191231
                             GROUP BY FLOOR(E.CLRQ / 100), D.PROVINCE, C.TD) Z
                     WHERE T.NY = Z.NY(+)
                       AND T.TD = Z.TD

                    UNION
                    SELECT TO_CHAR(Z.NY) 年月,
                           (SELECT NAME FROM TPROVINCE a WHERE IBM = Z.SF) 省份,
                           (SELECT DISTINCT (A.NAME)
                              FROM LBORGANIZATION A, TTDCY B
                             WHERE B.TD = Z.TD
                               AND B.ORGID = A.ID) 部门,
                           (SELECT TDMC FROM TTDBM a WHERE ID = Z.TD) 团队,
                           T.GRJE 个人金额,
                           T.GRSL 个人数量,
                           Z.TDJE 机构金额,
                           Z.TDSL 机构数量
                      FROM (SELECT FLOOR(E.CLRQ / 100) NY,
                                   D.PROVINCE SF,
                                   C.TD TD,
                                   SUM(A.HTJE) GRJE,
                                   COUNT(*) GRSL
                              FROM THTXX A, TKHXX B, TTDCY C, lbOrganization D, TFP_CPDM E
                             WHERE B.KHLX = 0
                               AND A.KHH = B.ID
                               AND A.KHJL = C.RYXX
                               AND C.ORGID = D.ID
                               AND A.CPID = E.ID
                               AND A.SFDXHT = 0AND E.CLRQ BETWEEN 20191101 AND 20191231
                             GROUP BY FLOOR(E.CLRQ / 100), D.PROVINCE, C.TD) T,
                           (SELECT FLOOR(E.CLRQ / 100) NY,
                                   D.PROVINCE SF,
                                   C.TD TD,
                                   SUM(A.HTJE) TDJE,
                                   COUNT(*) TDSL
                              FROM THTXX A, TKHXX B, TTDCY C, lbOrganization D, TFP_CPDM E
                             WHERE B.KHLX = 1
                               AND A.KHH = B.ID
                               AND A.KHJL = C.RYXX
                               AND C.ORGID = D.ID
                               AND A.CPID = E.ID
                               AND A.SFDXHT = 0AND E.CLRQ BETWEEN 20191101 AND 20191231
                             GROUP BY FLOOR(E.CLRQ / 100), D.PROVINCE, C.TD) Z
                     WHERE T.NY(+) = Z.NY
                       AND T.TD = Z.TD