sql - WHERE ID = MAX(ID) 获取错误聚合可能不会出现

Question

我在整理查询时遇到问题，我已按照 http://www.sql-server-helper.com/error-messages/msg-147.aspx 上的说明进行操作(页面底部)除了添加 WHERE 子句和内部连接外，我的代码与示例没有太大区别。

但我仍然收到以下错误:

Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

这是我自己的代码:

SELECT  *
FROM    [dbo].[mail] AS rm
    INNER JOIN [dbo].[mytbl] AS ec ON [rm].[webref] = [ec].[Webref]
WHERE   rm.[webref] = 'XYZ-594112'
    AND [PolRef@] = ( SELECT    MAX([PolRef@])
                      FROM      [dbo].[mytbl]
                    )

谁能解释为什么会发生这种情况？

编辑:

CREATE TABLE [dbo].[mail](
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [datetime] NULL,
[webref] [nvarchar](20) NULL
) ON [PRIMARY]  

另一个表实际上是一个 View ，它由几个其他表组成，但是简化版本是:

CREATE TABLE [dbo].[mytbl](
[PolRef@] [varchar](10) NULL,
[Webref] [varchar](30) NULL) ON [PRIMARY]

编辑更新:

现在出现以下错误:

SELECT  *
FROM    [FreshSystems].[dbo].[mail] AS rm
    INNER JOIN [dbo].[mytbl] AS ec ON [rm].[webref] = [ec].[Webref]
WHERE   rm.[webref] = 'XYZ-594112'
HAVING [PolRef@] = ( SELECT    MAX([PolRef@])
                      FROM      [dbo].[mytbl]
                    )

错误

Msg 8121, Level 16, State 1, Line 5
Column 'mytbl.PolRef@' is invalid in the HAVING clause because it is not     contained in either an aggregate function or the GROUP BY clause.

工作:

SELECT  *
FROM    [dbo].[mail] AS rm
LEFT OUTER JOIN  [dbo].[mytbl] AS ec ON [rm].[webref] = [ec].[Webref]
WHERE   rm.[webref] = 'XYZ-594112'
AND [PolRef@] = ( SELECT    MAX([PolRef@])
                  FROM      [dbo].[mytbl]
                  WHERE [Webref] = 'XYZ-594112'
                )

Answer 1

我会用这样的东西

select  *
from    [dbo].[mail] as rm
join    [dbo].[mytbl] as ec
on      [rm].[webref] = [ec].[webref]
join    ( select    max([polref@]) y
          from      [dbo].[mail]
        ) x
on      [polref@] = x.y
where   rm.[webref] = 'xyz-594112'