SQL 加入三个表或在oracle中做嵌套的sql语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6989721/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
join three tables or do nested sql statement in oracle
提问by petey
join three tables or do nested sql in oracle
加入三个表或在oracle中做嵌套sql
I have three tables: gut, pos and cfg
我有三个表：gut、pos 和 cfg
gut and pos share a value and a join can be made with
gut.gut_val= pos.pos_val.
gut 和 pos 共享一个值，并且可以使用
gut.gut_val= pos.pos_val.
Similarly the cfg and pos tables share a value and a join can be made with the following: cfg.cfg_dev_cot = pos.pos_dev_val.
同样，CFG和POS表共享值和加入可以与以下几个方面进行：cfg.cfg_dev_cot = pos.pos_dev_val。
However cfg and gut dont share a value.
What i want to do is display the values for gut_cou, pos_mon_runch and cfg_cfg_cou, when
gut_val = pos_valand cfg_dev_cot = pos_dev_val. Is there a way to do this with joins or is it best to do a nested sql statement?
但是 cfg 和gut 不共享一个值。我想要做的是显示gut_cou、pos_mon_runch 和cfg_cfg_cou、when
gut_val = pos_val和cfg_dev_cot = pos_dev_val. 有没有办法用连接来做到这一点，还是最好做一个嵌套的 sql 语句？
what i have tried is the following but it throws an oracle error.
我尝试过的是以下内容，但它会引发 oracle 错误。
select gut.gut_cou, pos.pos_mon_runch, cfg.cfg_cou
from gut,pos,cfg
where gut.gut_val = pos.pos_val
and cfg.cfg_dev_cot = pos.pos_dev_val
and pos.POS_CIT='12345654'
and gut.gut_DAT_DEB <= '08-AUG-11'
and gut.gut_DAT_FIN >= '08-AUG-11'
and gut.gut_TCV ='BOU'
回答by MatBailie
For a start, get rid of who-ever or what-ever taught you to use ,notation for joins. Instead, use something like the following.
首先，摆脱谁或曾经教过您使用,连接符号的人。相反，请使用以下内容。
This may not get rid of your error message, but you haven't told us what the error is ;)
这可能无法消除您的错误消息，但您还没有告诉我们错误是什么；)
SELECT
gut.gut_cou,
pos.pos_mon_ruch,
cfg.cfg_cou
FROM
gut
INNER JOIN
pos
ON pos.pos_val = gut.gut_val
INNER JOIN
cfg
ON cfg.cfg_dev_cot = pos.pos_dev_val
WHERE
pos.POS_CIT='12345654'
AND gut.gut_DAT_DEB <= '08-AUG-11'
AND gut.gut_DAT_FIN >= '08-AUG-11'
AND gut.gut_TCV ='BOU'
回答by Gary Myers
I'd avoid using
我会避免使用
and gut.gut_DAT_DEB <= '08-AUG-11'
and gut.gut_DAT_FIN >= '08-AUG-11'
and go for explicit conversion using a format mask and four digit years
并使用格式掩码和四位数年份进行显式转换
and gut.gut_DAT_DEB <= TO_DATE('08-AUG-2011','DD-MON-YYYY')
and gut.gut_DAT_FIN >= TO_DATE('08-AUG-2011','DD-MON-YYYY')
Also bear in mind that there MIGHT be a time component on the dates so you may wany
还要记住，日期上可能有时间部分，所以你可能很虚弱
and gut.gut_DAT_DEB <= TO_DATE('08-AUG-2011 23:59:59','DD-MON-YYYY HH24:MI:SS')
回答by Theofanis Pantelides
If all three tables do not share all the data, there will be rows not returned with your select.
如果所有三个表不共享所有数据，则您的选择将不会返回行。
Try the following:
请尝试以下操作：
SELECT g.gut_cou, p.pos_mon_runch, c.cfg_cou
FROM gut g, pos p
LEFT OUTER JOIN cfg c ON c.cfg_dev_cot = p.pos_dev_val
WHERE g.gut_val = p.pos_val
AND p.POS_CIT='12345654'
AND trunc(g.gut_DAT_DEB, 'DDD') <= TO_DATE('08-AUG-11','DD-MON-YY')
AND trunc(g.gut_DAT_FIN, 'DDD') >= TO_DATE('08-AUG-11','DD-MON-YY')
AND g.gut_TCV ='BOU'
