Question

我的发言非常简单,可以从几个表格中得出一些结果。其中一个栏目是用图表表生成的。虽然我似乎能够按该栏排列,但试图从该栏中过滤说该栏是无效的。我的法典和错误如下:

SELECT 
        e.Title as Title,
        -- Get earliest start time and latest end time
        (SELECT TOP 1 l.StartTime FROM Locations l WHERE l.EventID = e.EventID ORDER BY l.StartTime ASC) as EarliestStartTime,
        (SELECT TOP 1 l.EndTime FROM Locations l WHERE l.EventID = e.EventID ORDER BY l.EndTime DESC) as LatestEndTime

    FROM
        Events e
    WHERE
            e.UserID = @UserID
        AND LatestEndTime < DATEADD(DAY, GETDATE(), 1)
    ORDER BY
        EarliestStartTime ASC

错误:

Invalid column name  LatestEndTime .

如果我删除<条码>AND LatestEndtime < DATEADD(星期四,GETDATE(1),那么该声明是完美的。

Answer 1

您可参考<条码>中条款中的各栏。重复一栏定义:

WHERE  e.UserID = @UserID
       AND (SELECT TOP 1 l.EndTime FROM Locations l 
            WHERE l.EventID = e.EventID ORDER BY l.EndTime DESC) < 
            DATEADD(DAY, GETDATE(), 1)

或使用一个子序列来标明该栏:

select  *
from    (
        select  e.Title as Title,
                -- Get earliest start time and latest end time
                (SELECT TOP 1 l.StartTime FROM Locations l
                 WHERE l.EventID = e.EventID ORDER BY l.StartTime ASC)
                 as EarliestStartTime,
                (SELECT TOP 1 l.EndTime FROM Locations l
                 WHERE l.EventID = e.EventID ORDER BY l.EndTime DESC)
                 as LatestEndTime
        from    Events e
        ) as SubQuery
WHERE   UserID = @UserID
        and LatestEndTime < DATEADD(DAY, GETDATE(), 1)

Answer 2

可尝试使用。

它认为你无法确定“LatestEndtime”栏。它要么不存在,要么是MySQL在假数据库中检查,因为你使用了“位置和位置”。问题。

友情链接