四表联查,三张独立表,一张关联表

0.表关系说明

表mtest 与 ptest 是一对多的关系;
表ptest 与 stest 是一对多的关系;
表mtest 与 stest 是一对多的关系;
表pstest 是 mtest 与 stest 的关联表;

1. 独立表1 :mtest

CREATE TABLE `mtest` (
  `id` bigint(10) NOT NULL,
  `mcode` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `mname` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
);

2. 独立表2 :ptest

CREATE TABLE `ptest` (
  `id` bigint(10) NOT NULL,
  `mid` bigint(10) DEFAULT NULL,
  `pcode` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `pname` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
);

3. 独立表3 :stest

CREATE TABLE `stest` (
  `id` bigint(10) NOT NULL,
  `scode` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `sname` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `mid` bigint(10) DEFAULT NULL,
  `mcode` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
);

4. 关联表4 :pstest

CREATE TABLE `pstest` (
  `sid` bigint(10) DEFAULT NULL,
  `pid` bigint(10) DEFAULT NULL
);

5. 关联查询:

SELECT 
    mp.mcode,mp.mname,mp.pcode,mp.pname,psp.scode,psp.sname
FROM
    (
        SELECT m.mcode,m.mname,p.pcode,p.pname
        FROM 
        mtest m LEFT JOIN ptest p
        ON 
        m.id = p.mid
    ) mp
LEFT JOIN
    (
        SELECT p.pcode,p.pname,s.scode,s.sname
        FROM 
        ptest p,pstest ps,stest s
        WHERE
        p.id = ps.pid AND ps.sid = s.id
    ) psp
ON
    mp.pcode = psp.pcode AND
    mp.pname = psp.pname;

6. 关联查询-截图:

查询结果截图

7. 关联查询(实例):

SELECT 
    mp.merchant_code,mp.merchant_name_zh,mp.account_no,mp.account_name,psp.store_code,psp.store_name
FROM
    (
        SELECT m.merchant_code,m.merchant_name_zh,p.account_name,p.account_no
        FROM 
            (SELECT id,merchant_code,merchant_name_zh FROM mc_merchant WHERE status =0 AND approve_status = 3) m 
        LEFT JOIN 
            (SELECT id,account_no,account_name,merchant_id FROM mc_payee_info WHERE status = 0) p
        ON 
        m.id = p.merchant_id
    ) mp
LEFT JOIN
    (
        SELECT p.account_no,p.account_name,s.store_code,s.store_name
        FROM 
        mc_payee_info p,mc_payeestore_rel ps,mc_store s
        WHERE
        p.id = ps.payee_info_id AND ps.store_id = s.id
    ) psp
ON
    mp.account_no = psp.account_no AND
    mp.account_name = psp.account_name;

8. 关联查询+连接查询(union all) 拼上原本不符合条件的数据(实例):

SELECT merchantCode,merchantName,accountNo,accountName,storeCode,storeName 
FROM(
    SELECT MP.MERCHANT_CODE    merchantCode,
           MP.MERCHANT_NAME_ZH merchantName,
           MP.ID               accountNo,
           MP.ACCOUNT_NAME     accountName,
           PSP.STORE_CODE      storeCode,
           PSP.STORE_NAME      storeName
      FROM (SELECT M.MERCHANT_CODE, M.MERCHANT_NAME_ZH, P.ACCOUNT_NAME, P.ID
              FROM (SELECT ID, MERCHANT_CODE, MERCHANT_NAME_ZH
                      FROM MC_MERCHANT
                     WHERE STATUS = 0) M
              LEFT JOIN (SELECT ID, ACCOUNT_NAME, MERCHANT_ID
                          FROM MC_PAYEE_INFO
                         WHERE STATUS = 0) P
                ON M.ID = P.MERCHANT_ID) MP
      LEFT JOIN (SELECT P.ID, P.ACCOUNT_NAME, S.STORE_CODE, S.STORE_NAME
                   FROM MC_PAYEE_INFO P, MC_PAYEESTORE_REL PS, MC_STORE S
                  WHERE P.ID = PS.PAYEE_INFO_ID
                    AND PS.STORE_ID = S.ID) PSP
        ON MP.ID = PSP.ID
       AND MP.ACCOUNT_NAME = PSP.ACCOUNT_NAME
   )
UNION ALL
SELECT merchantCode,merchantName,accountNo,accountName,storeCode,storeName 
FROM(
    SELECT MM.MERCHANT_CODE merchantCode,
           MM.MERCHANT_NAME_ZH merchantName,
           999999 accountNo,
           '999999' accountName,
           S1.STORE_CODE storeCode,
           S1.STORE_NAME storeName
      FROM (SELECT MS.STORE_NAME,
                   MS.MERCHANT_ID,
                   MS.MERCHANT_CODE,
                   MS.STORE_CODE
              FROM MC_STORE MS
             WHERE MS.STATUS = 0
               AND NOT EXISTS
             (SELECT 1 FROM MC_PAYEESTORE_REL PS WHERE PS.STORE_ID = MS.ID)) S1,
           MC_MERCHANT MM
     WHERE S1.MERCHANT_ID = MM.ID
       AND S1.MERCHANT_CODE = MM.MERCHANT_CODE
       AND S1.MERCHANT_CODE != '1651200000001'
   );