请帮助将Tsql“隐式连接”转换为转化为明确的

发布于 2024-09-12 08:57:43 字数 1166 浏览 9 评论 0原文

抱歉,我几乎是一个 SQL 菜鸟。这必须适用于 MSFT SQL、Oracle 以及 Sybase。在下面的代码片段中,我需要更改 IJ.PO_id = KL.PO_idIJ 和 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+CDCD+EFEF+GHGH +IJIJ+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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

饮湿 2024-09-19 08:57:43

我认为应该是这样的:

FROM AB
JOIN CD ON AB.VU_code = CD.VU_code
JOIN IJ ON IJ.EF_id = AB.EF_id AND IJ.ZY_id = AB.ZY_id AND IJ.IJ_id = AB.IJ_id
JOIN EF ON EF.EF_id = IJ.EF_id AND EF.ZY_id = IJ.ZY_id
JOIN GH ON EF.XW_id = GH.GH_id
JOIN KL ON IJ.PO_id = KL.PO_id
WHERE
    IJ.IJ_id = @IJ_id AND
    IJ.TS > 0 AND
    IJ.RQ = 0 AND
    EF.RQ = 0 AND
    AB.RQ = 0

我尝试排列表,使以下规则成立:

  • 每个连接条件都提到它在一侧连接的新表。
  • 如果该表尚未连接,则连接条件中不会提及该表。
  • 操作数之一为常量的条件保留为 WHERE 条件。

最后一条规则是一个困难的规则 - 不可能从你的损坏的名称中判断一个条件是否应该是连接的一部分还是 where 子句的一部分。对于 INNER JOIN 两者都会给出相同的结果。条件应该是连接的一部分还是where 子句的一部分取决于表之间关系的语义。

您需要根据具体情况考虑每个条件:

  • 它是否定义了两个表之间的关系?将其放入 JOIN 中。
  • 它是对结果的过滤吗?将其放在 WHERE 子句中。

一些准则:

  • 包含来自用户的参数的条件不太可能是应移动到联接的内容。
  • 连接条件中通常不会发现不平等。

I think it should be something like this:

FROM AB
JOIN CD ON AB.VU_code = CD.VU_code
JOIN IJ ON IJ.EF_id = AB.EF_id AND IJ.ZY_id = AB.ZY_id AND IJ.IJ_id = AB.IJ_id
JOIN EF ON EF.EF_id = IJ.EF_id AND EF.ZY_id = IJ.ZY_id
JOIN GH ON EF.XW_id = GH.GH_id
JOIN KL ON IJ.PO_id = KL.PO_id
WHERE
    IJ.IJ_id = @IJ_id AND
    IJ.TS > 0 AND
    IJ.RQ = 0 AND
    EF.RQ = 0 AND
    AB.RQ = 0

I have tried to arrange the tables such that the following rules hold:

  • Every join condition mentions the new table that it joining on one side.
  • No table is mentioned in a join condition if that table has not been joined yet.
  • Conditions where one of the operands is a constant are left as a WHERE condition.

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:

  • Does it define the relationship between the two tables? Put it in the JOIN.
  • Is it a filter on the results? Put it in the WHERE clause.

Some guidelines:

  • A condition that includes a parameter from the user is unlikely to be something that should be moved to a join.
  • Inequalities are not usually found in join conditions.
久而酒知 2024-09-19 08:57:43

它的可读性不可能比你给出的例子低......

from AB a
join CD c on a.VU_Code = c.VU_Code
join EF e on a.EF_id = e.EF_id and e.RQ = 0
join GH g on e.XW_id = g.GH_id
join IJ i on a.IJ_id = i.IJ_id and e.EF_id = i.EF_id
         and a.EF_id = i.EF_id and e.ZY_id = i.ZY_id
         and a.ZY_id = i.ZY_id and i.TS > 0 and i.RQ = 0
LEFT join KL k on i.PO_id = k.PO_id
where 
    i.IJ_id = @IJ_id and 
    a.RQ = 0

It couldn't possibly get any less readable than the example you gave...

from AB a
join CD c on a.VU_Code = c.VU_Code
join EF e on a.EF_id = e.EF_id and e.RQ = 0
join GH g on e.XW_id = g.GH_id
join IJ i on a.IJ_id = i.IJ_id and e.EF_id = i.EF_id
         and a.EF_id = i.EF_id and e.ZY_id = i.ZY_id
         and a.ZY_id = i.ZY_id and i.TS > 0 and i.RQ = 0
LEFT join KL k on i.PO_id = k.PO_id
where 
    i.IJ_id = @IJ_id and 
    a.RQ = 0
懷念過去 2024-09-19 08:57:43

使用:

 FROM AB t1
 JOIN CD t2 ON t2.VU_code = t1.VU_code
 JOIN GH t4 ON t4.gh_id = t3.xw_id
 JOIN IJ t5 ON t5.ZY_id = t1.ZY_id
           AND t5.IJ_id = t1.IJ_id
           AND t5.EF_id = t1.EF_id 
           AND t5.IJ_id = @IJ_id 
           AND t5.TS > 0 
           AND t5.RQ = 0 
 JOIN EF t3 ON t3.ef_id = t5.ef_id
           AND t3.zy_id = t5.zy_id
           AND t3.RQ = 0
 JOIN KL t6 ON t6.po_id = t5.po_id -- Add LEFT before JOIN for LEFT JOIN
WHERE ab.qu = 0

它们按照原始 ANSI-89 语法的顺序设置别名,但由于别名引用而调整了顺序 - 在定义表别名之前无法引用表别名。

这是 ANSI-92 JOIN 语法 - 没有性能优势,但它确实意味着 OUTER join 语法是一致的。只需在“JOIN KL ...”之前添加 LEFT 即可将其转换为 LEFT JOIN。

Use:

 FROM AB t1
 JOIN CD t2 ON t2.VU_code = t1.VU_code
 JOIN GH t4 ON t4.gh_id = t3.xw_id
 JOIN IJ t5 ON t5.ZY_id = t1.ZY_id
           AND t5.IJ_id = t1.IJ_id
           AND t5.EF_id = t1.EF_id 
           AND t5.IJ_id = @IJ_id 
           AND t5.TS > 0 
           AND t5.RQ = 0 
 JOIN EF t3 ON t3.ef_id = t5.ef_id
           AND t3.zy_id = t5.zy_id
           AND t3.RQ = 0
 JOIN KL t6 ON t6.po_id = t5.po_id -- Add LEFT before JOIN for LEFT JOIN
WHERE ab.qu = 0

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文