日期:2014-05-18  浏览次数:20544 次

关于分组的问题
现有表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