select (sum(Faccrual)+sum(fsy)) as 已实现收益,sum(fetvalue) as 未实现收益, (sum(Faccrual)+sum(fsy)+sum(fetvalue)) as 期间投资收益合计 ,sum(fcost) as 期末平均持仓成本 ,sum(fMarketValue) as 期末市价, max(tb_secpossession.fname) as 证券名称,
sum(fetvalue) as 潜在收益 from tb_secpossession inner join tb_sectrade on tb_secpossession.fcode=tb_sectrade.fcode where tb_secpossession.fdate
between to_date('2008-02-13','yyyy-MM-dd') and to_date('2008-02-15','yyyy-MM-dd') and rownum<=5 group by tb_secpossession.fcode order by 期间投资收益合计 desc 我用rownum<=5为什么只能取到一条记录呢?
sum(fetvalue) as 潜在收益 from tb_secpossession inner join tb_sectrade on tb_secpossession.fcode=tb_sectrade.fcode where tb_secpossession.fdate
between to_date('2008-02-13','yyyy-MM-dd') and to_date('2008-02-15','yyyy-MM-dd') and rownum<=5 group by tb_secpossession.fcode order by 期间投资收益合计 desc 我用rownum<=5为什么只能取到一条记录呢?
select * from (
select (sum(Faccrual)+sum(fsy)) as 已实现收益,sum(fetvalue) as 未实现收益, (sum(Faccrual)+sum(fsy)+sum(fetvalue)) as 期间投资收益合计 ,sum(fcost) as 期末平均持仓成本 ,sum(fMarketValue) as 期末市价, max(tb_secpossession.fname) as 证券名称,
sum(fetvalue) as 潜在收益,rownum as num
from tb_secpossession
inner join tb_sectrade on tb_secpossession.fcode=tb_sectrade.fcode
where tb_secpossession.fdate between to_date('2008-02-13','yyyy-MM-dd') and to_date('2008-02-15','yyyy-MM-dd')
group by tb_secpossession.fcode order by 期间投资收益合计 desc
) where num<=5
这样如果你tb_secpossession.fcode 一样的纪录超过5条了,就只取5条
然后就变成了只有一个tb_secpossession.fcode
再group by 自然只有一条
如果tb_secpossession.fcode一样的,只有4条,那就会看到2条纪录(实际上前四条的tb_secpossession.fcode
是一样的,然后再第二个tb_secpossession.fcode
这样一group by 就出来两条
)