关于分组的问题
现有表A
id type DateTime amount
1 a 2007-7-1 2
2 a 2007-7-2 6
3 a 2007-7-3 5
4 b 2007-7-2 3
5 b 2007-7-3 4
查询结果：
id type MaxDateTime amount sumAmount
3 a 2007-7-3 5 13
5 b 2007-7-3 4 7
求出总数和最大时间对应的行
[解决办法]
Select
A.*,
B.sumAmount
From
A
Inner Join
(Select type, Max([DateTime]) As MaxDateTime, SUM(amount) As sumAmount From A Group By type) B
On A.type = B.type And A.[DateTime] = B.MaxDateTime
[解决办法]
select id,a1.type,maxdatetime, a1.amount,sumAmount from A a1
inner join (select type,max([datetime]) as maxdatetime,sum(amount) as sumAmount from A
group by type)b
on a1.type=b.type and a1.datetime=b.maxdatetime
--结果：
id type maxdatetime amount sumAmount
----- ------ -------------------------- ------- -----------
3 a 2007-07-03 00:00:00.000 5 13
5 b 2007-07-03 00:00:00.000 4 7
[解决办法]
select a.id ,a.type,x.maxdatetime,a.amount,x.sumamount
from (select type ,max(datetime) maxdatetime,sum(amount) sumamount
from a
group by type ) x
join a
on a.type=x.type
where a.datetime=x.maxdatetime
