tsql - 如何限制为 1 年期限

Question

此查询返回 3 年的记录。如何将其更改为返回（最近的）1 年的记录？

select id, format(ual.inserted, 'yyyy-MM-dd') as inserted
from ual
where id = 347877
group by id, format(ual.inserted, 'yyyy-MM-dd')
having format(ual.inserted, 'yyyy-MM-dd') >= dateadd(year,-1,max(inserted))
order by format(ual.inserted, 'yyyy-MM-dd') desc

我想要做的是返回最近 1 年的记录id。

编辑@Squirrel 发表评论后所做的更改：

select id, format(ual.inserted, 'yyyy-MM-dd') as inserted
from ual
where webid = 347877 and format(ual.inserted, 'yyyy-MM-dd') >= dateadd(year,-1,max(inserted))
order by format(ual.inserted, 'yyyy-MM-dd') desc

此查询产生此错误：

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.

标签： tsqlsql-server-2012having

Answer 1

使用子查询找到maxofinserted然后在where

select id, format(ual.inserted, 'yyyy-MM-dd') as inserted
from   ual
where id = 347877
and   ual.inserted >= (
                          select dateadd(year,-1,max(inserted))
                          from   ual x
                          where  x.id = ual.id
                      )
order by format(ual.inserted, 'yyyy-MM-dd') desc