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)
1.遗留问题 怎么找到 相差的呐俩条数据 ？
2. 问题到底出在哪里 ？