我可以只对2列5行进行分组吗？

Question

我有两个联合的SQL查询，但是有60个结果，在第二个查询中除了前2列(供应商和供应商id)什么都没有，其余的字段都是空的。第一个查询返回所有列中的结果。我想要显示这60个结果的第一组，但不是第二组，但是只有前两列匹配？这有没有可能。我相信我应该知道答案！

例如：

select 
    Supplier_name,
    Supplier_id,
    Bank_name,
    Bank_Branch
from suppliers,
     banks
where s=b and payables
union
select 
    Supplier_name,
    Supplier_id,
    Bank_name,
    Bank_Branch
from suppliers,
     banks
where s=b and <> payables

可能会返回

AKD, 1234, HSBC, London
AKD, 1234, NULL, NULL

我不想要第二排...

此外，以下是完整的查询：

SELECT
    HZP.PARTY_NAME "SUPPLIER_NAME" ,
    APS.SEGMENT1 "SUPPLIER_NUMBER",
    IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,
    IEB.BANK_ACCOUNT_NAME "BANK_ACCOUNT_NAME" ,
    HZPBANK.PARTY_NAME "BANK_NAME",
    IEB.IBAN,
    /*hzp.party_id hzp_party_id,
    APS.party_id aps_party_id,
    IEP.PAYEE_PARTY_ID IEP_PAYEE_PARTY_ID,
    hzp.party_id hzp_party_id1,
    IEP.EXT_PAYEE_ID IEP_EXT_PAYEE_ID,
    IPI.EXT_PMT_PARTY_ID IPI_EXT_PMT_PARTY_ID,
    ipi.instrument_id ipi_instrument_id,
    ieb.ext_bank_account_id,
    ieb.bank_id ieb_bank_id,
    hzpbank.party_id hzpbank_party_id,*/
    ibb.eft_swift_code,
    ibb.bank_branch_name,
    'Supplier' Record_type,
    HZP.country
FROM HZ_PARTIES HZP ,
    AP_SUPPLIERS APS ,
    IBY_EXTERNAL_PAYEES_ALL IEP ,
    IBY_PMT_INSTR_USES_ALL IPI ,
    IBY_EXT_BANK_ACCOUNTS IEB ,
    HZ_PARTIES HZPBANK,
    iby_ext_bank_branches_v IBB
WHERE HZP.PARTY_ID = APS.PARTY_ID
    --AND hzpbank.party_id = IBB.bank_party_id(+)
    AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID(+)
    AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID(+)
    AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID(+)
    AND IEB.BANK_ID = HZPBANK.PARTY_ID(+)
    AND IEP.PARTY_SITE_ID IS NULL
    AND IEP.ORG_ID IS NULL 
    AND IEP.ORG_TYPE IS NULL 
    AND IEP.SUPPLIER_SITE_ID IS NULL 
    --and hzp.party_name like 'RDF C%'
    AND ipi.payment_function = 'PAYABLES_DISB'
    --AND hzp.party_name = 'AKD'
    AND HZP.country = 'NL'
    AND ieb.branch_id = ibb.branch_party_id(+)

union

SELECT
    HZP.PARTY_NAME "SUPPLIER_NAME" ,
    APs.segment1 "SUPPLIER_NUMBER",
    NULL "BANK_ACCOUNT_NUMBER" ,
    NULL "BANK_ACCOUNT_NAME" ,
    NULL "BANK_NAME",
    NULL "IBAN",
    /*hzp.party_id hzp_party_id,
    APS.party_id aps_party_id,
    IEP.PAYEE_PARTY_ID IEP_PAYEE_PARTY_ID,
    hzp.party_id hzp_party_id1,
    IEP.EXT_PAYEE_ID IEP_EXT_PAYEE_ID,
    IPI.EXT_PMT_PARTY_ID IPI_EXT_PMT_PARTY_ID,
    ipi.instrument_id ipi_instrument_id,
    ieb.ext_bank_account_id,
    ieb.bank_id ieb_bank_id,
    hzpbank.party_id hzpbank_party_id,*/
    NULL "eft_swift_code",
    NULL "bank_branch_name",
    'Supplier' Record_type,
    HZP.country
FROM HZ_PARTIES HZP ,
    AP_SUPPLIERS APS ,
    IBY_EXTERNAL_PAYEES_ALL IEP ,
    IBY_PMT_INSTR_USES_ALL IPI ,
    IBY_EXT_BANK_ACCOUNTS IEB ,
    HZ_PARTIES HZPBANK,
    iby_ext_bank_branches_v IBB
WHERE HZP.PARTY_ID = APS.PARTY_ID
    --AND hzpbank.party_id = IBB.bank_party_id(+)
    AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID(+)
    AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID(+)
    AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID(+)
    AND IEB.BANK_ID = HZPBANK.PARTY_ID(+)
    AND IEP.PARTY_SITE_ID IS NULL
    AND IEP.ORG_ID IS NULL 
    AND IEP.ORG_TYPE IS NULL 
    AND IEP.SUPPLIER_SITE_ID IS NULL 
    --and hzp.party_name like 'RDF C%'
    --AND ipi.payment_function = 'PAYABLES_DISB'
    --AND hzp.party_name = 'AKD'
    AND HZP.country = 'NL'
    AND ieb.branch_id = ibb.branch_party_id(+)

Answer 1

首先--如果您不想要第二个查询的结果，那么为什么要将它与第一个查询联合起来呢？

但是如果你无法控制它，那么你可以这样做：

SELECT
   ...
   ...
FROM
   (
      SELECT query 1
      UNION
      SELECT query 2
   ) a
WHERE
   field 3 is NOT NULL
   AND field 4 is NOT NULL