在SQL中获取日期范围(Get Date Ranges in SQL)

 Oracle通常不会识别嵌套子查询中多于一个级别的表别名（或其他）; 从文档 ： 
 
 
 
 当嵌套子查询从引用子查询上一级的父语句的表引用列时，Oracle执行相关子查询。 [...]对于由父语句处理的每一行，概念上相关的子查询将被评估一次。 
 
 
 请注意“单层”部分。 因此，在嵌套子查询中，您的qm别名无法识别，因为它与qm别名的定义相距两级。 （如果你没有别名，原始表名也会发生同样的事情 - 这不是专门用于别名的）。 
 
 当你修改你的查询时，只需select qm.mdl_mdl_id as Vehicle_colour - 或者它的一个有效版本，或许(select qm.mdl_mdl_id from dual) as Vehicle_colour - 你删除了嵌套，并且qm现在只有一个级别定义在查询的主体中，因此得到了认可。 
 
 您在第一个嵌套子查询中对md引用可能不会被识别，但解析器往往会向后排序，所以它首先看到qm问题; 尽管可能重写查询会使其有效： 
 
 
 
 但是，优化器可能会选择将查询重写为联接，或者使用其他一些技术来制定语义等价的查询。 
 
 
 你也可以添加提示来鼓励，但最好不要依赖于此。 
 
 但是你不需要嵌套子查询，你可以在每个顶级子查询中加入： 
 
select (
        select mc2.make_description
        from model_colours@dblink1 mc1,
            make_colours@dblink1 mc2  
        where mc2.makc_id = mc1.makc_makc_id
        and to_char(mc1.mdc_id) = md.allocate_vehicle_colour_id
    ) as colour,
    (
        select mc2.make_description
        from model_colours@dblink1 mc1,
            make_colours@dblink1 mc2
        where mc2.makc_id = mc1.makc_makc_id
        and mc1.mdl_mdl_id = qm.mdl_mdl_id
    ) as vehicle_colour
from schema1.web_order wo,
...
 
 我坚持使用旧式连接语法来匹配主查询，但您应该考虑用现代ANSI连接语法重写整个事物。 （我也删除了提到的流氓逗号@Serg，但在发布问题时，您可能只是在实际选择列表中忽略了其他列。） 
 
 通过连接到主查询中的make和model颜色表，可以完全避免子查询，可以两次处理单独的过滤条件，或者在列表达式中使用一些逻辑。 虽然一次一步，但... 

Oracle usually doesn't recognise table aliases (or anything else) more than one level down in a nested subquery; from the documentation:
 
 
 
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. [...] A correlated subquery conceptually is evaluated once for each row processed by the parent statement.
 
 
Note the 'one level' part. So your qm alias isn't being recognised where it is, in the nested subquery, as it is two levels away from the definition of the qm alias. (The same thing would happen with the original table name if you hadn't aliased it - it isn't specifically to do with aliases).
 
When you modified your query to just have select qm.mdl_mdl_id as Vehicle_colour - or a valid version of that, maybe (select qm.mdl_mdl_id from dual) as Vehicle_colour - you removed the nesting, and the qm was now only one level down from it's definition in the main body of the query, so it was recognised.
 
Your reference to md in the first nested subquery probably won't be recognised either, but the parser tends to sort of work backwards, so it's seeing the qm problem first; although it's possible a query rewrite would make it valid:
 
 
 
However, the optimizer may choose to rewrite the query as a join or use some other technique to formulate a query that is semantically equivalent.
 
 
You could also add hints to encourage that but it's better not to rely on that.
 
But you don't need nested subqueries, you can join inside each top level subquery:
 
select (
        select mc2.make_description
        from model_colours@dblink1 mc1,
            make_colours@dblink1 mc2  
        where mc2.makc_id = mc1.makc_makc_id
        and to_char(mc1.mdc_id) = md.allocate_vehicle_colour_id
    ) as colour,
    (
        select mc2.make_description
        from model_colours@dblink1 mc1,
            make_colours@dblink1 mc2
        where mc2.makc_id = mc1.makc_makc_id
        and mc1.mdl_mdl_id = qm.mdl_mdl_id
    ) as vehicle_colour
from schema1.web_order wo,
...
 
I've stuck with old-style join syntax to match the main query, but you should really consider rewriting the whole thing with modern ANSI join syntax. (I've also removed the rogue comma @Serg mentioned, but you may just have left out other columns in your real select list when posting the question.)
 
You could probably avoid subqueries altogether by joining to the make and model colour tables in the main query, either twice to handle the separate filter conditions, or once with a bit of logic in the column expressions. Once step at a time though...