加入CTE时出现SQL Server语法错误

user3115933 发表于 Dev

用户名

我正在使用SQL Server 2014，并且在下面的SQL查询中遇到问题。似乎某个地方存在语法错误，但我找不到它。

我正在从两个单独的视图（即ReservationList和）中加入两个选择查询ARACCOUNT。

USE MyDatabase

;WITH q1 AS (
    select * 
    from ReservationList
    WHERE [Market Segment Code] <> 'COMP' AND 
    NOT (Status] = 'CANCELED' OR [Status] = 'NOSHOW' OR [Status] = 'WAITLIST') AND 
    NOT [Departure Date] < '2014-07-01' AND 
    NOT [Market Segment Code] = 'COMP' AND 
    NOT [Arrival Date] = [Departure Date]
)a

q2 AS (
    SELECT 
        [PMSConfirmationNumber],
        [FOC ACCOUNT],
        [AR ACCOUNT NAME]
    FROM 
        ARACCOUNT
)b

Select 
    q1.* 
from 
    q1
    JOIN q2 ON q1.PmsConfirmatioNnumber = q2.foh_PmsConfirmationNumber

错误消息如下：

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ')'.

我还需要从ARACCOUNT中选择的字段出现在最终输出中。如何修改查询以包括我的第二季度选择？

穆雷尼克

您在两个CTE之间缺少逗号。另外，删除别名-定义CTE时不需要它们：

;WITH q1 AS (


 select * 

 from ReservationList

 WHERE [Market Segment Code] <> 'COMP'

 AND NOT ([Status] = 'CANCELED' OR [Status] = 'NOSHOW' OR [Status] = 'WAITLIST')

 AND NOT [Departure Date] < '2014-07-01'

 AND NOT [Market Segment Code] = 'COMP'

 AND NOT [Arrival Date] = [Departure Date]

), -- alias dropped and comma added here

q2 AS (

SELECT [PMSConfirmationNumber],[FOC ACCOUNT],[AR ACCOUNT NAME]

 FROM ARACCOUNT

) -- alias dropped

Select q1.*, q2.* from q1
JOIN q2 ON q1.PmsConfirmatioNnumber = q2.foh_PmsConfirmationNumber