现在有一张表,有n个人的购买记录,每个记录里面有时间标识.
我想取出每个用户最后购买的那条记录(3个字段).
表结构:
iacctid --- 客户的id
iamount --- 客户钱包的余额
dtLastMdfyTime ---最后购买时间
我写成这样了:
select iacctid, sum(iamount)
from
t_cost
where
iqpoint = 0
and statis_date < '20060301'
group by iacctid
having
dtLastMdfyTime = max(dtLastMdfyTime)
,可是最后一句语法有错.请高手赐教:)
我想取出每个用户最后购买的那条记录(3个字段).
表结构:
iacctid --- 客户的id
iamount --- 客户钱包的余额
dtLastMdfyTime ---最后购买时间
我写成这样了:
select iacctid, sum(iamount)
from
t_cost
where
iqpoint = 0
and statis_date < '20060301'
group by iacctid
having
dtLastMdfyTime = max(dtLastMdfyTime)
,可是最后一句语法有错.请高手赐教:)
select t_cost.*,rank() over(partition by iacctid order by dtLastMdfyTime desc) rn from t_cost
)
where rn=1
having
dtLastMdfyTime = max(dtLastMdfyTime)
这里的问题.group by iacctid 后就不可能再有dtLastMdfyTime 了,最多只有iacctid ,当然max(dtLastMdfyTime)还是有的.