1 create table buyer as 
  2 SELECT b.id AS bankid
  3   FROM v_product_deal_main m, base_member b
  4  WHERE b.id = m.BUYERID
  5    AND m.DEALDATE <= to_date(‘20160630‘, ‘yyyymmdd‘)
  6    AND m.STATE = 9
  7    AND b.state = 0;
  8 ---441947
  9 --seller方
 10  create table seller as SELECT * FROM seller ;
 11 create table seller as 
 12 SELECT b.id AS bankid
 13   FROM v_product_deal_main m, base_member b
 14  WHERE b.id = m.sellerid
 15    AND m.DEALDATE <= to_date(‘20160630‘, ‘yyyymmdd‘)
 16    AND m.STATE = 9
 17    AND b.state = 0;
 18      
 19     
 20 create TABLE ttt as
 21 SELECT aa,bankid
 22 FROM (SELECT 1 aa,
 23         m.BUYERID AS bankid
 24 FROM v_product_deal_main m, base_member b
 25 WHERE b.id = m.BUYERID
 26 AND m.DEALDATE <= to_date(‘20160630‘, ‘yyyymmdd‘)
 27 AND m.STATE = 9
 28 AND b.state = 0
 29 UNION ALL
 30 SELECT 2 AS aa,
 31         m.sellerID AS bankid
 32 FROM v_product_deal_main m, base_member b
 33 WHERE b.id = m.sellerid
 34 AND m.DEALDATE <= to_date(‘20160630‘, ‘yyyymmdd‘)
 35 AND m.STATE = 9
 36 AND b.state = 0);
 37 --从下面可以看到 seller 表中的行数+ buyer 表中的行数  < ttt表中的行数 ；
 38 SELECT COUNT(1)  FROM seller ;  --441945
 39 SELECT COUNT(1)  FROM buyer ;   --441947
 40 SELECT COUNT(1)   FROM ttt ;   ---883894
 41 -- bankid=1 在buyer表中出现的次数 15191 + （seller表中出现的次数）11457= （ttt 表中出现的次数）30382 ;（seller + buyer）= 26648
 42 --4378 +5859  =8756 10237 --bankid =2 出现的次数 
 43 SELECT 4378 +5859 FROM dual ;
 44 SELECT  b.bankid ,COUNT(b.bankid)  FROM buyer b GROUP BY b.bankid  ORDER BY b.bankid ;
 45 SELECT  s.bankid ,COUNT(s.bankid)  FROM seller s GROUP BY s.bankid ORDER BY s.bankid ;
 46 SELECT t.bankid ,COUNT(t.bankid)  FROM ttt t  GROUP BY t.bankid  ORDER BY t.bankid  
 47 
 48 
 49 SELECT SUM(DECODE(t.aa ,1 ,bankid,0 )) buyer ,
 50       SUM( DECODE(t.aa ,2 ,bankid,0 )) seller
 51  FROM ttt t ;
 52  
 53 
 54 SELECT SUM(DECODE(t.aa ,1 ,1,0 )) buyer ,
 55       SUM( DECODE(t.aa ,2 ,1,0 )) seller
 56  FROM ttt t ;
 57 
 58 
 59 --441947
 60 SELECT bankid, count(1) from ttt
 61 where aa=2  
 62 MINUS
 63 --441945
 64 Select bankid ,Count(1) From seller ;
 65 
 66 SELECT bankid from ttt
 67 where aa=2  
 68 MINUS
 69 --441945
 70 Select bankid  From seller ;
 71 SELECT COUNT(1) from ttt
 72 
 73 -------------------------------
 74 --1765  --441947
 75 select bankid,count(1) from ttt
 76 where aa=2
 77 group by bankid
 78 order by bankid ;
 79 
 80 --1465  --441945
 81 Select bankid,Count(1) From seller
 82 group by bankid
 83 order by bankid
 84 ;
 85 
 86  
 87  SELECT t.bankid FROM ttt t WHERE t.aa =1  
 88  
 89  SELECT t.bankid FROM ttt t WHERE t.aa =2  
 90  
 91  
 92  
 93 SELECT SUM(NVL(BUYERID, 0) + NVL(SELLERID, 0))
 94   FROM (SELECT (SELECT 1
 95                   FROM BASE_MEMBER B
 96                  WHERE B.ID = M.BUYERID
 97                    AND B.STATE = 0) AS BUYERID,
 98                (SELECT 1
 99                   FROM BASE_MEMBER B
100                  WHERE B.ID = M.SELLERID
101                    AND B.STATE = 0) AS SELLERID
102           FROM V_PRODUCT_DEAL_MAIN M
103          WHERE M.DEALDATE <= TO_DATE(‘20160630‘, ‘yyyymmdd‘)
104            AND M.STATE = 9)