1 Create TableBuyer as 2 SELECTb.ID asBankID3 fromV_product_deal_main m, Base_member b4 WHEREb.ID=M.buyerid5 andM.dealdate<=To_date ('20160630','YYYYMMDD')6 andM.state= 97 andB.state= 0;8 ---4419479 --Seller SquareTenCreate table seller asSELECT * fromseller; One Create TableSeller as A SELECTb.ID asBankID - fromV_product_deal_main m, Base_member b - WHEREb.ID=M.sellerid the andM.dealdate<=To_date ('20160630','YYYYMMDD') - andM.state= 9 - andB.state= 0; - + - Create TABLETtt as + SELECTAa,bankid A from(SELECT 1AA, atM.buyerid asBankID - fromV_product_deal_main m, Base_member b - WHEREb.ID=M.buyerid - andM.dealdate<=To_date ('20160630','YYYYMMDD') - andM.state= 9 - andB.state= 0 in UNION All - SELECT 2 asAA, toM.sellerid asBankID + fromV_product_deal_main m, Base_member b - WHEREb.ID=M.sellerid the andM.dealdate<=To_date ('20160630','YYYYMMDD') * andM.state= 9 $ andB.state= 0);Panax Notoginseng--from below you can see the number of rows in the seller table + the number of rows in the buyer table < rows in the TTT table; - SELECT COUNT(1) fromSeller;--441945 the SELECT COUNT(1) fromBuyer;--441947 + SELECT COUNT(1) fromTTT;---883894 A --Number of occurrences of bankid=1 in the buyer table15191 + (number of occurrences in seller table) 11457= (number of occurrences in the TTT table) 30382; (seller + Buyer) = 26648 the --4378 +5859 =8756 10237--bankid =2 number of occurrences + SELECT 4378 +5859 fromdual; - SELECTB.bankid,COUNT(B.bankid) fromBuyer bGROUP byB.bankidORDER byB.bankid; $ SELECTS.bankid,COUNT(S.bankid) fromSeller SGROUP byS.bankidORDER byS.bankid; $ SELECTT.bankid,COUNT(T.bankid) fromTTT TGROUP byT.bankidORDER byT.bankid - - the SELECT SUM(DECODE (T.AA,1, BankID,0)) buyer, - SUM(DECODE (T.AA,2, BankID,0)) SellerWuyi fromTTT T; the - Wu SELECT SUM(DECODE (T.AA,1,1,0)) buyer, - SUM(DECODE (T.AA,2,1,0)) Seller About fromTTT T; $ - - --441947 - SELECTBankID,Count(1) fromTTT A whereAa=2 + minus the --441945 - SelectBankID,Count(1) fromseller; $ the SELECTBankID fromTTT the whereAa=2 the minus the --441945 - SelectBankID fromseller; in SELECT COUNT(1) fromTTT the the ------------------------------- About --1765--441947 the SelectBankID,Count(1) fromTTT the whereAa=2 the Group byBankID + Order byBankID; - the --1465--441945Bayi SelectBankID,Count(1) fromSeller the Group byBankID the Order byBankID - ; - the the SELECTT.bankid fromTTT TWHERET.aa=1 the the SELECTT.bankid fromTTT TWHERET.aa=2 - the the the SELECT SUM(NVL (Buyerid,0)+NVL (Sellerid,0))94 from(SELECT(SELECT 1 the fromBase_member B the WHEREb.ID=M.buyerid the andB.state= 0) asBuyerid,98(SELECT 1 About fromBase_member B - WHEREb.ID=M.sellerid101 andB.state= 0) asSellerid102 fromV_product_deal_main M103 WHEREM.dealdate<=To_date ('20160630','YYYYMMDD')104 andM.state= 9)
1. How to find the difference between the two data? 2. Where exactly is the problem?
The
uses union all to encounter problems (the sum of the number of rows of the two SQL statements and not the amount of rows after union ALL!) How to find the difference between the two data?