MySQL嵌套左连接与外部引用

Question

假设我们有这些(从更复杂的表格简化而来)示例表格:

== st ==    == pr ===   == rn ===  <– tables
sta   pg    pg    rou   sta   rou  <– fields
========    =========   =========
H1    aa    aa    aaA   H1    aaA

H2    aa    aa    aaB   H2    aaB
H3    aa                H3    aaB

H4    aa    aa    aaC   H4    aaC
H5    aa                H5    aaC
H6    aa                H6    aaC

H7    aa

H8    bb    bb    NULL

我想用内部左连接执行这个(也是简化的)查询:

SELECT st.*, pr.*, rn.*
FROM         st
INNER JOIN ( pr
LEFT  JOIN   rn  ON pr.rou = rn.rou
                AND          rn.sta = st.sta -- ERROR here
           )     ON pr.pg =           st.pg
-- filter out bad rows
WHERE ( rn.id )                          -- a: not null
   OR ( pr.rou ='aaC' AND rn.id IS NULL) -- b: no joinable rn found: choose
                                         --    by a predefined pr.rou value
   OR ( pr.rou IS NULL )                 -- c: no need to join

得到这个结果:

== st ==    == pr ===   == rn ===
sta   pg    pg    rou   sta   rou
========    =========   =========
H1    aa    aa    aaA   H1    aaA
H2    aa    aa    aaB   H2    aaB
H3    aa    aa    aaB   H3    aaB
H4    aa    aa    aaC   H4    aaC
H5    aa    aa    aaC   H5    aaC
H6    aa    aa    aaC   H6    aaC

H7    aa    aa    aaA   NULL  NULL \  H7 has no rn, so choose
H7    aa    aa    aaB   NULL  NULL  } 1 row of these at the
H7    aa    aa    aaC   NULL  NULL /  WHERE / b condition

H8    bb    bb    NULL  NULL  NULL

但是 MySQL 抛出了这个错误:#1054 - Unknown column 'st.sta' in 'on clause'。

我试图解决这个问题但没有成功，直到有人发布(并删除)了两次加入 rn 专栏的好主意。多亏了他/她，我才能够创建一个可行的解决方案:

SELECT st.*, pr.*, rn.*, rn2.*

FROM         st
INNER JOIN   pr         ON st.pg = pr.pg
LEFT  JOIN   rn         ON st.sta = rn.sta
LEFT  JOIN   rn AS rn2  ON pr.rou = rn2.rou -- the two rn's join order is important
                       AND rn.id  = rn2.id  -- if first exists, second must match or null, first null => second null
WHERE ( rn.id = rn2.id )                    -- a: both not null
   OR ( pr.rou ='aaC' AND rn.id IS NULL)    -- b: no joinable rn found: choose by predefined pr.rou value
   OR ( pr.rou IS NULL )                    -- c: no need to join

虽然此查询有效，但它需要以正确的顺序重复连接，非常脆弱且丑陋。

你能给出一个更简洁的解决方案，它只加入 rn 表一次吗？

这里是用于复制粘贴的 sql 示例数据库，如果您想使用它:

DROP    TABLE IF     EXISTS st;
CREATE  TABLE IF NOT EXISTS st (
  id  int  AUTO_INCREMENT,
  sta varchar(9),
  pg  varchar(9),
  PRIMARY KEY ( id )
)  AUTO_INCREMENT=1;

DROP    TABLE IF     EXISTS pr;
CREATE  TABLE IF NOT EXISTS pr (
  id  int  AUTO_INCREMENT,
  pg  varchar(9),
  rou varchar(9),
  PRIMARY KEY ( id )
)  AUTO_INCREMENT=1;

DROP    TABLE IF     EXISTS rn;
CREATE  TABLE IF NOT EXISTS rn (
  id  int  AUTO_INCREMENT,
  sta varchar(9),
  rou varchar(9),
  PRIMARY KEY ( id )
)  AUTO_INCREMENT=1;


INSERT INTO st 
(sta , pg ) VALUES
('H1','aa'),
('H2','aa'),
('H3','aa'),
('H4','aa'),
('H5','aa'),
('H6','aa'),
('H7','aa'),
('H8','bb');
INSERT INTO pr
( pg , rou ) VALUES
('aa','aaA'),
('aa','aaB'),
('aa','aaC'),
('bb', NULL);
INSERT INTO rn
(sta , rou ) VALUES
('H1','aaA'),
('H2','aaB'),
('H3','aaB'),
('H4','aaC'),
('H5','aaC'),
('H6','aaC');

Answer 1

我想我终于明白了:

SELECT st.*, pr.*, rn.*
FROM st
  LEFT JOIN rn ON st.sta = rn.sta
  LEFT JOIN pr ON st.pg  = pr.pg
              AND (rn.rou = pr.rou OR rn.rou IS NULL)