Допустим, у нас есть ( упрощенный из более сложного) пример таблиц:
== 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 - Неизвестный столбец 'st.sta' в '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');