select (case when a.keyname=b.keyname then sum(a.amount) end) as sum_all from a,(select accountid,keyname from a where postiondate=max(positiondate) group by accountid,keyname) b where a.accountid=b.accoutid
繁了一點,但可用 select ff,sum(amount) from (select ff,amount from (select keyname as ff,max(PositionDate) as aa from test2 group by accountid,keyname) as bb inner join test2 on aa=test2.PositionDate and ff=test2.keyname)as dd group by ff
select keyName,sum(Amount) from table a where PositionDate= (select Max(PositionDate) from table b where a.AccountID<>b.AccountID )and keyname=(select Max(keyname) from table c where a.AccountID<>c.AccountID ) group by keyName
slect sum(amount) ,keyname from (select * form utable a , (select id max(positiondate) from utable group by accountid) b where a.id=b.id) c group by keyname
declare @2 table (accountid Nvarchar(10),keyName Nvarchar(10),Amount INT,postiondate datetime) insert @2 values ('AA','MM',600,'2003-04-24') insert @2 values ('AA','MM',300,'2003-04-15') insert @2 values ('AA','HH',800,'2003-04-24') insert @2 values ('BB','HH',30,'2003-04-13') insert @2 values ('BB','MM',20,'2003-04-18') --insert @2 values ('BB','',null,'')Select c.keyName As keyName,Sum(c.Amount) As Amount From (select a.* from @2 a, (select AccountID,keyName,Max(postiondate) As postiondate From @2 Group By AccountID,keyName) b where a.AccountID=b.AccountID And a.PostionDate=b.postiondate And a.keyName=b.keyName) c Group By c.keyName
to cbzdream(迷茫) 上面的运行结果应该是你想要的结果吧,如果不是,能否麻烦你说一下我理解错了哪一点?
1. select AccountID,keyName,Amount,PositionDate from table1 as A where ABS(datediff(dd, PositionDate, @edate)) = (select min(ABS(datediff(dd, PositionDate, @edate))) from table1 where AccountID = A.AccountID and keyName = A.keyName) 2. select keyName, sum(Amount) from ( select AccountID,keyName,Amount,PositionDate from table1 as A where ABS(datediff(dd, PositionDate, @edate)) = (select min(ABS(datediff(dd, PositionDate, @edate))) from table1 where AccountID = A.AccountID and keyName = A.keyName) ) as B group by B.keyName
以AccountID分组,去PositionDate最大的记录
AccountID keyName Amount PositionDate
---------- - - ------- --------- --------------
AAA MMM 60000 2003-04-24
BBB MMM 2000 2003-04-18
然后求出Amount的和即62000
以AccountID分组,去PositionDate最大的记录
AccountID keyName Amount PositionDate
---------- - - ------- --------- --------------
AAA MMM 60000 2003-04-24
BBB MMM 2000 2003-04-18
然后求出keyname相同时Amount的和即62000
from a,(select accountid,keyname from a where postiondate=max(positiondate) group by accountid,keyname) b
where a.accountid=b.accoutid
select ff,sum(amount) from
(select ff,amount from
(select keyname as ff,max(PositionDate) as aa from test2 group by accountid,keyname) as bb inner join test2 on aa=test2.PositionDate
and ff=test2.keyname)as dd
group by ff
slect sum(amount) ,keyname from
(select * form utable a ,
(select id max(positiondate) from utable group by accountid) b
where a.id=b.id) c
group by keyname
insert @2 values ('AA','MM',600,'2003-04-24')
insert @2 values ('AA','MM',300,'2003-04-15')
insert @2 values ('AA','HH',800,'2003-04-24')
insert @2 values ('BB','HH',30,'2003-04-13')
insert @2 values ('BB','MM',20,'2003-04-18')
--insert @2 values ('BB','',null,'')Select c.keyName As keyName,Sum(c.Amount) As Amount From
(select a.* from @2 a,
(select AccountID,keyName,Max(postiondate) As postiondate From @2 Group By AccountID,keyName) b
where a.AccountID=b.AccountID And a.PostionDate=b.postiondate And a.keyName=b.keyName) c
Group By c.keyName
上面的运行结果应该是你想要的结果吧,如果不是,能否麻烦你说一下我理解错了哪一点?
select AccountID,keyName,Amount,PositionDate from table1 as A
where ABS(datediff(dd, PositionDate, @edate)) =
(select min(ABS(datediff(dd, PositionDate, @edate))) from table1
where AccountID = A.AccountID and keyName = A.keyName)
2.
select keyName, sum(Amount) from (
select AccountID,keyName,Amount,PositionDate from table1 as A
where ABS(datediff(dd, PositionDate, @edate)) =
(select min(ABS(datediff(dd, PositionDate, @edate))) from table1
where AccountID = A.AccountID and keyName = A.keyName)
) as B
group by B.keyName