使用HAVING子句中max函数的正确方式是先进行自连接:
select t1.a, t1.b, t1.c
from table1 t1
join table1 t1_max
on t1.id = t1_max.id
group by t1.a, t1.b, t1.c
having t1.date = max(t1_max.date)
以下是如何与子查询联接的示例:
select t1.a, t1.b, t1.c
from table1 t1
where t1.date = (select max(t1_max.date)
from table1 t1_max
where t1.id = t1_max.id)
在处理多表联接时,请确保创建单个数据集后再使用聚合函数:
select t1.id, t1.date, t1.a, t1.b, t1.c
into #dataset
from table1 t1
join table2 t2
on t1.id = t2.id
join table2 t3
on t1.id = t3.id
select a, b, c
from #dataset d
join #dataset d_max
on d.id = d_max.id
having d.date = max(d_max.date)
group by a, b, c
子查询版本:
select t1.id, t1.date, t1.a, t1.b, t1.c
into #dataset
from table1 t1
join table2 t2
on t1.id = t2.id
join table2 t3
on t1.id = t3.id
select a, b, c
from #dataset d
where d.date = (select max(d_max.date)
from #dataset d_max
where d.id = d_max.id)