select keyName, sum(Amount) from (select AccountID , keyName , Amount , PositionDate from tab a where positionDate = (select max(positiondate) from tab b where b.accountid = a.accountid) as temptab) group by keyname
select accountid,keyName,sum(amount),max(postiondate) group by accoutid,keyname
select AccountID, 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 AccountID你试试吧!
to demiurge: 不行,有错误: as 附近有错。我的表名是Position
to 哈哈 有错: 服务器: 消息 8120,级别 16,状态 1,行 12 列 'a.KeyName' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
sorry!写错了 select AccountID, 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 AccountID,keyName
select AccountID,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 AccountID,keyName
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
to 哈哈: 还是不对: 你的结果: AccountID keyName Amount AAA MMM 60000
select keyname,sum(amount) from position a where positiondate= ( select max(positiondate) from position b where a.accountid=b.accounti and a.keyname=b.keyname ) group by keyname
where positionDate = (select max(positiondate) from tab b where b.accountid = a.accountid) as temptab)
group by keyname
group by accoutid,keyname
不行,有错误: as 附近有错。我的表名是Position
有错:
服务器: 消息 8120,级别 16,状态 1,行 12
列 'a.KeyName' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
select AccountID, 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 AccountID,keyName
结果不对。我要求出:
以AccountID分组,去PositionDate最大的记录
AccountID keyName Amount PositionDate
---------- - - ------- --------- --------------
AAA MMM 60000 2003-04-24
BBB MMM 2000 2003-04-18
然后求出Amount的和即62000
from a,(select accountid,keyname from a where postiondate=max(positiondate) group by accountid,keyname) b
where a.accountid=b.accoutid
还是不对:
你的结果:
AccountID keyName Amount
AAA MMM 60000
where positiondate=
( select max(positiondate) from position b
where a.accountid=b.accounti and a.keyname=b.keyname )
group by keyname