我目前有几个 5 表,我想将表 1 内部连接到表 2,然后表连接到表 2 到表 3,依此类推......
我已经尝试过使用很少的分离内部连接来做到这一点,但是如何仅用 1 个 SQL 获得相同的结果
SELECT table1.CW_S_EVT,table2.CW_S_TYP, table2.CW_S_SERVER
FROM table1 INNER JOIN table2
ON table1.CW_S_EVT = table2.CW_S_EVT;
SELECT table2.CW_S_EVT,table2.CW_S_TYP, table3.CW_S_TPL
FROM table2 INNER JOIN table3
ON table2 .CW_S_TYP = table3.CW_S_TYP
SELECT table3.CW_S_TPL, table4.CW_L_TPL,table4.CW_CONTENT
FROM table3 INNER JOIN table4
ON table3.CW_S_TPL = table4.CW_S_TPL
SELECT table1.CW_S_EVT,table5.CW_S_VAR, table5.CW_L_VAR
FROM table1 INNER JOIN table5
ON table1.CW_S_EVT = table5.CW_S_EVT;
这是我在互联网上找到的代码,但它与我的情况不匹配。
SELECT *
FROM table1
INNER JOIN table2
ON table1.primaryKey=table2.table1Id
INNER JOIN table3
ON table1.primaryKey=table3.table1Id
您需要的是left join
您的标准。由于这将导致null
值,因此添加coalesce
函数将能够帮助我们提供符合您条件的值。
如果需要,附加distinct
关键字。
SELECT distinct coalesce(table1.CW_S_EVT, table2.CW_S_EVT, table3.CW_S_TPL)
, coalesce(table2.CW_S_TYP, table4.CW_L_TPL, table5.CW_S_VAR)
, coalesce(table2.CW_S_SERVER, table3.CW_S_TPL, table4.CW_CONTENT, table5.CW_L_VAR)
FROM table1
LEFT JOIN table2 ON table1.CW_S_EVT = table2.CW_S_EVT;
LEFT JOIN table3 ON table2.CW_S_TYP = table3.CW_S_TYP
LEFT JOIN table4 ON table3.CW_S_TPL = table4.CW_S_TPL
LEFT JOIN table5 ON table1.CW_S_EVT = table5.CW_S_EVT;
试试这个
SELECT
table1.CW_S_EVT as t1_CW_S_EVT,
table2.CW_S_TYP as t2_CW_S_TY,
table2.CW_S_SERVE as t2_CW_S_SERVE,
table2.CW_S_EVT as t2_CW_S_EVT,
table3.CW_S_TPL as t3_CW_S_TPL,
table4.CW_L_TPL as t4_CW_L_TPL,
table4.CW_CONTENT as t4_CW_CONTENT,
table5.CW_S_VAR as t5_CW_S_VAR,
table5.CW_L_VAR as t5_CW_L_VAR
FROM
table1, table2, table3, table4, table5
where
table1.CW_S_EVT = table2.CW_S_EVT AND
table2 .CW_S_TYP = table3.CW_S_TYP AND
table3.CW_S_TPL = table4.CW_S_TPL AND
table1.CW_S_EVT = table5.CW_S_EVT;
SELECT table1.CW_S_EVT,table3.CW_S_TYP,table4.CW_S_TPL, table5.CW_S_VAR
FROM table1
INNER JOIN table2 ON table1.CW_S_EVT = table2.CW_S_EVT
INNER JOIN table3 ON table2.CW_S_TYP = table3.CW_S_TYP
INNER JOIN table4 ON table3.CW_S_TPL = table4.CW_S_TPL
INNER JOIN table5 ON table1.CW_S_EVT = table5.CW_S_EVT
感谢您的所有友好回答,但我发现这终于起作用了,juz 记录在这里也许是为了将来使用.^^