create table buyer as

 SELECT b.id AS bankid

   FROM v_product_deal_main m, base_member b

  WHERE b.id = m.BUYERID

    AND m.DEALDATE <= to_date('', 'yyyymmdd')

    AND m.STATE = 9

    AND b.state = 0;

 ---441947

 --seller方

  create table seller as SELECT * FROM seller ;

 create table seller as

 SELECT b.id AS bankid

   FROM v_product_deal_main m, base_member b

  WHERE b.id = m.sellerid

    AND m.DEALDATE <= to_date('', 'yyyymmdd')

    AND m.STATE = 9

    AND b.state = 0;

 create TABLE ttt as

 SELECT aa,bankid

 FROM (SELECT 1 aa,

         m.BUYERID AS bankid

 FROM v_product_deal_main m, base_member b

 WHERE b.id = m.BUYERID

 AND m.DEALDATE <= to_date('', 'yyyymmdd')

 AND m.STATE = 9

 AND b.state = 0

 UNION ALL

 SELECT 2 AS aa,

         m.sellerID AS bankid

 FROM v_product_deal_main m, base_member b

 WHERE b.id = m.sellerid

 AND m.DEALDATE <= to_date('', 'yyyymmdd')

 AND m.STATE = 9

 AND b.state = 0);

 --从下面可以看到 seller 表中的行数+ buyer 表中的行数  < ttt表中的行数 ；

 SELECT COUNT(1)  FROM seller ;  --

 SELECT COUNT(1)  FROM buyer ;   --

 SELECT COUNT(1)   FROM ttt ;   ---883894

 -- bankid=1 在buyer表中出现的次数 15191 + （seller表中出现的次数）11457= （ttt 表中出现的次数）30382 ;（seller + buyer）= 26648

 --4378 +5859  =8756 10237 --bankid =2 出现的次数

 SELECT 4378 +5859 FROM dual ;

 SELECT  b.bankid ,COUNT(b.bankid)  FROM buyer b GROUP BY b.bankid  ORDER BY b.bankid ;

 SELECT  s.bankid ,COUNT(s.bankid)  FROM seller s GROUP BY s.bankid ORDER BY s.bankid ;

 SELECT t.bankid ,COUNT(t.bankid)  FROM ttt t  GROUP BY t.bankid  ORDER BY t.bankid  

 SELECT SUM(DECODE(t.aa ,1 ,bankid,0 )) buyer ,

       SUM( DECODE(t.aa ,2 ,bankid,0 )) seller

  FROM ttt t ;

 SELECT SUM(DECODE(t.aa ,1 ,1,0 )) buyer ,

       SUM( DECODE(t.aa ,2 ,1,0 )) seller

  FROM ttt t ;

 --

 SELECT bankid, count(1) from ttt

 where aa=2

 MINUS

 --

 Select bankid ,Count(1) From seller ;

 SELECT bankid from ttt

 where aa=2

 MINUS

 --

 Select bankid  From seller ;

 SELECT COUNT(1) from ttt

 -------------------------------

 --1765  --441947

 select bankid,count(1) from ttt

 where aa=2

 group by bankid

 order by bankid ;

 --1465  --441945

 Select bankid,Count(1) From seller

 group by bankid

 order by bankid

 ;

  SELECT t.bankid FROM ttt t WHERE t.aa =1  

  SELECT t.bankid FROM ttt t WHERE t.aa =2  

 SELECT SUM(NVL(BUYERID, 0) + NVL(SELLERID, 0))

   FROM (SELECT (SELECT 1

                   FROM BASE_MEMBER B

                  WHERE B.ID = M.BUYERID

                    AND B.STATE = 0) AS BUYERID,

                (SELECT 1

                   FROM BASE_MEMBER B

                  WHERE B.ID = M.SELLERID

                    AND B.STATE = 0) AS SELLERID

           FROM V_PRODUCT_DEAL_MAIN M

          WHERE M.DEALDATE <= TO_DATE('', 'yyyymmdd')

            AND M.STATE = 9)