sql - 列出连接表记录

Question

我有一组表格如下:

客户(cus_id,cus_name)

账户(acc_num,acc_balance,bra_code)

分支机构(bra_code,bra_address)

如果我想列出所有拥有多个帐户的客户、分支机构及其详细信息。初学oracle 希望大家多多帮助。

这实际上是我作业的一部分，到目前为止我所做的是这样的..

SELECT  DISTINCT 
        C.CUS_ID, 
        (C.CUS_FIRST_NAME || ' ' || C.CUS_LAST_NAME) AS CUS_NAME,
        C.CUS_IC, 
        C.CUS_ADDRESS, 
        C.CUS_POSTCODE, 
        C.CUS_CONTACT, 
        C.CUS_EMAIL, 
        C.CUS_AGE,     
        C.CUS_GENDER, 
        C.CUS_STATUS,
        B.BRA_CODE, 
        B.BRA_ADDRESS, 
        B.BRA_POSTCODE, 
        R.REG_STATE, 
        R.REG_COUNTRY
FROM CUSTOMER C, ACCOUNT A,BRANCH B, REGION R
WHERE C.CUS_ID=A.CUS_ID AND 
      A.BRA_CODE=B.BRA_CODE AND 
      B.REG_ID=R.REG_ID AND 
      A.BRA_CODE IN (SELECT A.BRA_CODE
                     FROM CUSTOMER C, ACCOUNT A,BRANCH B
                     WHERE C.CUS_ID=A.CUS_ID AND A.BRA_CODE=B.BRA_CODE
                     GROUP BY A.BRA_CODE HAVING COUNT(A.BRA_CODE)>1)
GROUP BY 
        C.CUS_ID,
        C.CUS_FIRST_NAME,
        C.CUS_LAST_NAME,
        C.CUS_IC, 
        C.CUS_ADDRESS, 
        C.CUS_POSTCODE, 
        C.CUS_CONTACT, 
        C.CUS_EMAIL, 
        C.CUS_AGE, 
        C.CUS_GENDER, 
        C.CUS_STATUS, 
        B.BRA_CODE, 
        B.BRA_ADDRESS, 
        B.BRA_POSTCODE, 
        R.REG_STATE, 
        R.REG_COUNTRY 
        HAVING COUNT(C.CUS_ID)>1;

Answer 1

试试这个:

SELECT *
FROM customer c, account a, branch b
WHERE c.cus_id IN (
        SELECT a2.cus_id
        FROM account a2, branch b2
        WHERE a2.bra_code = b2.bra_code
        GROUP BY a2.cus_id
        HAVING COUNT(DISTINCT(b2.bra_code)) > 1
    )
    AND c.cus_id = a.cus_id
    AND a.bra_code = b.bra_code