TSQL 查询和 CTE 查询的语法

Question

我将在这里的这篇文章末尾发布我的查询，但首先需要说明。请忽略列名和表名，但我在两个地方有语法错误。当我将此查询与我的 CTE 一起放置时，它告诉我必须首先"用 ; 终止前面的语句。然后我在CTE中为列名别名，然后它说"无法绑定多部分标识符"E.ActiveAGV"。

我希望我能很好地解释我的问题。如果有人能看到我正在尝试做什么，并让我知道它是否有效或纠正我的语法错误，我将不胜感激。

Select A.move_hour as 'Hour', 
       isnull(B.move_count,0) as 'Current_Count', 
       isnull(C.move_count,0) as '1_Day_Previous', 
       isnull(D.move_count,0) as '2_Day_Previous',
       ISNULL (E.ActiveAGVs,0) as 'Active AGV''s'
           --^ Error right here
from
   (select distinct(DATEPART(HH, Move_History.Move_Dt)) as move_hour 
   from Move_History 
   where Plant_Id = 1 and Building_Id = 1) as A
left outer join
   (select datepart(HH,Move_History.Move_Dt) as move_hour, 
           Move_History.Move_Cnt as move_count 
   from Move_History 
Group by datepart(HH,Move_History.Move_Dt), Move_Cnt) as B on A.move_hour = B.move_hour
left outer join
(select datepart(HH,Move_History.Move_Dt) as move_hour, Move_History.Move_Cnt as       move_count 
from Move_History 
Group by datepart(HH,Move_History.Move_Dt), Move_Cnt) as C on A.move_hour = C.move_hour
left outer join
(select datepart(HH,Move_History.Move_Dt) as move_hour, Move_History.Move_Cnt as move_count 
from Move_History 
Group by datepart(HH,Move_History.Move_Dt), Move_Cnt) as D on A.move_hour = D.move_hour;
with const as (
    select cast(cast(getdate() as date) as datetime) as midnight
    ),
allhours as (
    select 0 as m_hour, midnight as timestart, dateadd(hour, 1, midnight) as timeend from const union all
   ...
    select 23 as m_hour, dateadd(hour, 23, midnight) as timestart, dateadd(hour, 24, midnight) as timeend from const
   ) 
(select ah.m_hour,
   (sum(datediff(SECOND, timestart), ah.timeend else dt.End_Dt end)) 
   / 18000.0) * 5 as ActiveAGVs              
from allhours as ah
 left outer join AGV_Report as dt
 on ah.timestart< coalesce(dt.End_dt, getdate()) and
    ah.timeend >= dt.Begin_Dt
Group by datepart(SECOND,ah.hour), ah.timestart) as E on A.move_hour = E.move_hour
                                   --^ 'Incorrect syntax near "as"'
 where A.move_hour is not null
order by ah.m_hour asc

Answer 1

您需要为语句定义的所有 CTE 都必须位于语句的开头。即使 CTE 将仅在其中一个子查询中使用，语法仍要求将它们放在整个语句的开头，而不是实际引用它们的特定子查询的开头。

因此，您的语句可能如下所示：

;  -- required if there are statements preceding
with const as (
    select cast(cast(getdate() as date) as datetime) as midnight
    ),
allhours as (
    select
       0 as m_hour,
       midnight as timestart,
       dateadd(hour, 1, midnight) as timeend
    from const
    union all
   ...
    select
       23 as m_hour,
       dateadd(hour, 23, midnight) as timestart,
       dateadd(hour, 24, midnight) as timeend
    from const
   )
Select A.move_hour as 'Hour', 
       isnull(B.move_count,0) as 'Current_Count', 
       isnull(C.move_count,0) as '1_Day_Previous', 
       isnull(D.move_count,0) as '2_Day_Previous',
       ISNULL (E.ActiveAGVs,0) as 'Active AGV''s'
from
   (
      select distinct
         DATEPART(HH, Move_History.Move_Dt) as move_hour 
      from Move_History 
      where Plant_Id = 1 and Building_Id = 1
   ) as A
left outer join
   (
      select
         datepart(HH,Move_History.Move_Dt) as move_hour, 
         Move_History.Move_Cnt as move_count 
      from Move_History 
      Group by datepart(HH,Move_History.Move_Dt), Move_Cnt
   ) as B on A.move_hour = B.move_hour
left outer join
   (
      select
         datepart(HH,Move_History.Move_Dt) as move_hour,
         Move_History.Move_Cnt as move_count 
      from Move_History 
      Group by datepart(HH,Move_History.Move_Dt), Move_Cnt
   ) as C on A.move_hour = C.move_hour
left outer join
   (
      select
         datepart(HH,Move_History.Move_Dt) as move_hour,
         Move_History.Move_Cnt as move_count 
      from Move_History 
      Group by datepart(HH,Move_History.Move_Dt), Move_Cnt
   ) as D on A.move_hour = D.move_hour
left outer join  -- assuming...
   (
      select
         ah.m_hour,
         (sum(datediff(SECOND, timestart), ah.timeend else dt.End_Dt end))
         / 18000.0) * 5 as ActiveAGVs              
      from allhours as ah
      left outer join AGV_Report as dt
         on ah.timestart < coalesce(dt.End_dt, getdate())
        and ah.timeend >= dt.Begin_Dt
      Group by datepart(SECOND,ah.hour), ah.timestart
   ) as E on A.move_hour = E.move_hour
where A.move_hour is not null
order by ah.m_hour asc

Answer 2

公用

表表达式 （CTE） 必须用分号 （;) 分隔与任何先前语句分隔。 如果with前面没有语句，则不需要分号。

您可以在单个 with 中有多个 CTE。 示例如下：

with
  -- Counting numbers.
  Numbers as (
    select 1 as Number
    union all
    select Numbers.Number + 1
      from Numbers
      where Number < 10 ),
  -- Squares of counting numbers.
  Squares as (
    select Number, Number * Number as Square
      from Numbers )
  -- Result.
  select Number, Square
    from Squares

像其他试图提供帮助的人一样，我不知道你的SQL试图做什么。