MS SQL：嵌套选择 - 神秘的“无效列名”错误

Question

当我对 MSSQL 2000 运行以下查询时，

SELECT 
    DISTINCT(Email),
    (SELECT TOP 1 ActivityID
        FROM Activity aa, ActivityType tt 
        WHERE aa.ActivityTypeId = tt.ActivityTypeId 
            AND aa.ConsumerID = c.ConsumerID
            AND tt.ActivityType = 'Something_OptIn') optin,
    (SELECT TOP 1 ActivityID
        FROM Activity aa, ActivityType tt 
        WHERE aa.ActivityTypeId = tt.ActivityTypeId 
            AND aa.ConsumerID = c.ConsumerID
            AND tt.ActivityType = 'Something_OptOut') optout
FROM
    Activity a,
    Consumer c,
    ActivityType t
WHERE
    c.CountryID = '23'
    AND t.ActivityType = 'Something_Create'
    AND a.ActivityTypeId = t.ActivityTypeId
    AND c.ConsumerID = a.ConsumerID
    AND optin > 1

出现以下错误

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'optin'.

为什么会发生这种情况？我不明白为什么它会无效。

Answer 1

SQL Server 不允许您在同一级别通过名称引用别名。要解决此问题，请重复列定义：

WHERE
    c.CountryID = '23'
    AND t.ActivityType = 'Something_Create'
    AND a.ActivityTypeId = t.ActivityTypeId
    AND c.ConsumerID = a.ConsumerID
    AND (SELECT TOP 1 ActivityID
        FROM Activity aa, ActivityType tt 
        WHERE aa.ActivityTypeId = tt.ActivityTypeId 
            AND aa.ConsumerID = c.ConsumerID
            AND tt.ActivityType = 'Something_OptIn'
        ) > 1

或使用子查询：

SELECT  *
FROM    (
        SELECT 
            DISTINCT(Email),
            (...) optin,
            (...) optout
        FROM
            Activity a,
            Consumer c,
            ActivityType t
        ) as SubqueryAlias
WHERE
    c.CountryID = '23'
    AND t.ActivityType = 'Something_Create'
    AND a.ActivityTypeId = t.ActivityTypeId
    AND c.ConsumerID = a.ConsumerID
    AND optin > 1

Answer 2

最后一行 AND optin > 1 是犯罪者。

WHERE 子句对 SELECT 列表中的列别名一无所知。

您可能应该在没有违规条件的情况下对此 SELECT 进行子查询，并将该条件应用于外部 SELECT。

SELECT *
FROM (
  SELECT
  ...
  WHERE ... /* everything except 'optin > 1' */
) anyAlias
WHERE optin > 1