奇怪的 SQL Server 视图定义 [英] Weird SQL Server view definition

问题描述

我继承"了一个已有 10 多年历史的应用，它有时确实会显示其年龄.我今天偶然发现了一个非常奇怪的视图定义 - 我似乎无法理解它！你能帮助我吗?这最初是在 SQL Server 7.0 上，后来迁移到 SQL Server 2005 - 但显然它从未被重构/重做......

I've "inherited" a well over 10-year old app, and it does show its age at times. I've stumbled across a really weird view definition today - I just can't seem to make sense of it! Can you help me? This was originally on SQL Server 7.0 and has since been migrated to SQL Server 2005 - but obviously it's never been refactored / redone....

这是视图定义 - 基于一堆表和另一个视图:

This is the view definition - based on a bunch of tables and another view:

CREATE VIEW dbo.MyOddView
AS
SELECT     
    t1.MVOID, t1.SomeOtherColumn, 
    t2.Number , 
    t3.OID, t3.FKOID,
    t4.AcctNo, 
    t5.ShortDesc, t5.ZipCode, t5.City, 
    t6.BankAcctNo
FROM
    dbo.viewFirst vf
INNER JOIN
    dbo.Table1 t1 ON vf.MVOID = t1.MVOID AND vf.ValidFrom = t1.ValidFrom 
LEFT OUTER JOIN
    dbo.Table2 t2 
RIGHT OUTER JOIN
    dbo.Table3 t3 ON t2.OID = t3.FKOID 
LEFT OUTER JOIN
    dbo.Table4 t4 ON t3.ZVOID = t4.OID 
LEFT OUTER JOIN
    dbo.Table5 t5
INNER JOIN
    dbo.Table4 t6 ON t5.OID = t6.BCOID 
    ON t4.ZVOID = t5.OID 
    ON t2.AddressOID = t4.OID
GO

我没有得到的是两个 JOIN(对于 Table2 t2 和 Table5 t5)，它们旁边没有列出 JOIN 条件，还有两个额外的 ON 视图定义末尾的条件 - 我似乎无法将其拆开并以正确的"ANSI JOIN 语法将其重新组合在一起，以便我的行数相同......(我的原始视图让我得到了超过 12'000 行的数据，并且第一次尝试重构它返回了一行......)

What I don't get are the two JOIN's (for Table2 t2 and Table5 t5) which have no JOIN condition listed next to them, and the two extra ON conditions at the end of the view definition - I can't seem to rip this apart and put it back together in "proper" ANSI JOIN syntax so that my row count is the same...... (my original view gets me something over 12'000 rows, and a first attempt at refactoring this returned a single row......)

有什么想法吗?这是什么鬼?对我来说似乎完全无效的 SQL - 但它似乎正在做它的工作(并且在过去几年里一直......)有什么想法吗?指针?

Any ideas? What the heck is this? Seems like totally invalid SQL to me - but it appears to be doing its job (and has been for the past several years....) Any thoughts? Pointers?

推荐答案

SELECT ...
FROM   dbo.viewFirst vf
       INNER JOIN dbo.Table1 t1
         ON vf.MVOID = t1.MVOID
            AND vf.ValidFrom = t1.ValidFrom
       LEFT OUTER JOIN dbo.Table2 t2
                       RIGHT OUTER JOIN dbo.Table3 t3
                         ON t2.OID = t3.FKOID
                       LEFT OUTER JOIN dbo.Table4 t4
                         ON t3.ZVOID = t4.OID
                       LEFT OUTER JOIN dbo.Table5 t5
                                       INNER JOIN dbo.Table4 t6
                                         ON t5.OID = t6.BCOID
                         ON t4.ZVOID = t5.OID
         ON t2.AddressOID = t4.OID  

Inside SQL Server 2008 T-SQL Querying 的第 7 章中介绍了此语法或参见 这篇文章 由 Itzik Ben Gan 和 跟进信 由 Lubor Kollar

This syntax is covered in chapter 7 of Inside SQL Server 2008 T-SQL Querying or see this article by Itzik Ben Gan and the follow up letter by Lubor Kollar

以 t2.AddressOID = t4.OID 最后的 ON 子句为例，表示 t2 的 JOIN代码> 逻辑上发生在最后.即首先逻辑地处理其他连接，然后LEFT JOIN针对这些连接的结果发生.

Having the ON clause for t2.AddressOID = t4.OID last for example means that the JOIN of t2 logically happens last. i.e the other joins are logically processed first then the LEFT JOIN happens against the result of those Joins.

这篇关于奇怪的 SQL Server 视图定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！