The sum of the number of rows that are encountered with the union ALL (the sum of the number of lines of both SQL statements and not equal to UNION ALL)! How to find the difference between the two data?

Source: Internet
Author: User

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?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.