数据库如下:
id customerid autor mydate
1 1 aa 2007-4-1
2 1 bb 2007-4-5
3 2 cc 2007-4-4
就像这样一个,
要获得customerid相同的的数量,
还要获得customerid相同的时间最后的一条记录
我的解决办法是:
Select a.Cnt,c.*
From
(Select customerid,Count(*) as Cnt From 表名 Group by customerid) a
Inner Join
(Select customerid,Max(mydate) as LastDate From 表名 Group by customerid) b
on a.customerid=b.customerid
Inner Join
表名 c on a.customerid=c.customerid And b.LastDate =c.mydate
id customerid autor mydate
1 1 aa 2007-4-1
2 1 bb 2007-4-5
3 2 cc 2007-4-4
就像这样一个,
要获得customerid相同的的数量,
还要获得customerid相同的时间最后的一条记录
我的解决办法是:
Select a.Cnt,c.*
From
(Select customerid,Count(*) as Cnt From 表名 Group by customerid) a
Inner Join
(Select customerid,Max(mydate) as LastDate From 表名 Group by customerid) b
on a.customerid=b.customerid
Inner Join
表名 c on a.customerid=c.customerid And b.LastDate =c.mydate