请帮助将Tsql“隐式连接”转换为转化为明确的
抱歉,我几乎是一个 SQL 菜鸟。这必须适用于 MSFT SQL、Oracle 以及 Sybase。在下面的代码片段中,我需要更改 IJ.PO_id = KL.PO_id
IJ 和 KL
之间的 inner join
> 也在 IJ.PO_id = KL.PO_id
上进行左连接
。所以,我相信我必须重新考虑这一点。好吧,隐式连接并不是最具可读性的,至少在我的同事看来是这样。我想我会同意,直到我形成自己的品味。抱歉,为了以防万一,我损坏了表和字段名称。
/* @IJ_id is an input stored proc patrameter. */
from AB,
CD,
EF,
GH,
IJ,
KL
where
EF.EF_id = IJ.EF_id and
IJ.EF_id = AB.EF_id and
EF.ZY_id = IJ.ZY_id and
IJ.ZY_id = AB.ZY_id and
IJ.IJ_id = AB.IJ_id and
IJ.IJ_id = @IJ_id and
EF.XW_id = GH.GH_id and
AB.VU_code = CD.VU_code and
IJ.TS > 0 and
IJ.RQ = 0 and
EF.RQ = 0 and
AB.RQ = 0 and
IJ.PO_id = KL.PO_id;
现在,我的困难是 where 子句中发生了很多事情。看起来不像 ab = cd
的内容将保留在 where 子句中,但并非所有看起来像 ab = cd
的内容看起来都很容易转换为显式联接。困难的部分是,理想情况下,条件是在邻居之间 - AB+CD
、CD+EF
、EF+GH
、GH +IJ
、IJ+KL
但它们现在还没有那么有组织。我可以重新排序一些,但最终我不想忘记我的目标:我希望新查询不慢,并且我希望新查询的可读性同样好。看来我最好只修改我需要更改的部分,并使其基本保持不变。我不确定我是否能做到这一点。
如果您理解我的意图,请提出更好的查询。如果你没有,那么请告诉我如何改进这个问题。谢谢。
Sorry, I am pretty much an SQL noob. This has to work in MSFT SQL, Oracle as well as Sybase. In the following snippet I need to change an inner join
between IJ
and KL
on IJ.PO_id = KL.PO_id
into a left join
also on IJ.PO_id = KL.PO_id
. So, I believe I have to re-factor this. Well, implicit joins are not the most readable, at least in my co-worker's eyes. I guess I will agree until I develop my own taste. Sorry, I mangled the table and field names just in case.
/* @IJ_id is an input stored proc patrameter. */
from AB,
CD,
EF,
GH,
IJ,
KL
where
EF.EF_id = IJ.EF_id and
IJ.EF_id = AB.EF_id and
EF.ZY_id = IJ.ZY_id and
IJ.ZY_id = AB.ZY_id and
IJ.IJ_id = AB.IJ_id and
IJ.IJ_id = @IJ_id and
EF.XW_id = GH.GH_id and
AB.VU_code = CD.VU_code and
IJ.TS > 0 and
IJ.RQ = 0 and
EF.RQ = 0 and
AB.RQ = 0 and
IJ.PO_id = KL.PO_id;
Now, my difficulty is that there is a lot going on in the where clause. Things that do not look like a.b = c.d
will remain in the where clause, but not all stuff that does look like a.b = c.d
look easy to convert into an explicit join. The difficult part is that ideally the conditions would be between neighbors - AB+CD
, CD+EF
, EF+GH
, GH+IJ
, IJ+KL
but they are not that organized right now. I could re-order some, but ultimately I do not want to forget my goal: I want the new query to be no slower, and I want the new query to be no less readable. It seems that I might be better off hacking just the part that I need to change, and leave it mostly the same. I am not sure if I can do that.
If you understood my intent, please suggest a better query. if you did not, then please tell me how I can improve the question. Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我认为应该是这样的:
我尝试排列表,使以下规则成立:
最后一条规则是一个困难的规则 - 不可能从你的损坏的名称中判断一个条件是否应该是连接的一部分还是 where 子句的一部分。对于 INNER JOIN 两者都会给出相同的结果。条件应该是连接的一部分还是where 子句的一部分取决于表之间关系的语义。
您需要根据具体情况考虑每个条件:
一些准则:
I think it should be something like this:
I have tried to arrange the tables such that the following rules hold:
The last rule is a difficult one - it is not possible to tell from your mangled names whether a condition ought to be part of a join or part of the where clause. Both will give the same result for an INNER JOIN. Whether the condition should be part of the join or part of the where clause depends on the semantics of the relationship between the tables.
You need to consider each condition on a case-by-case basis:
Some guidelines:
它的可读性不可能比你给出的例子低......
It couldn't possibly get any less readable than the example you gave...
使用:
它们按照原始 ANSI-89 语法的顺序设置别名,但由于别名引用而调整了顺序 - 在定义表别名之前无法引用表别名。
这是 ANSI-92 JOIN 语法 - 没有性能优势,但它确实意味着 OUTER join 语法是一致的。只需在“JOIN KL ...”之前添加 LEFT 即可将其转换为 LEFT JOIN。
Use:
They're aliased in the sequence of the original ANSI-89 syntax, but the order is adjusted due to alias reference - can't reference a table alias before it's been defined.
This is ANSI-92 JOIN syntax - there's no performance benefit, but it does mean that OUTER join syntax is consistent. Just have to add LEFT before the "JOIN KL ..." to turn that into a LEFT JOIN.