一个销售表sales:
workername salemoney
a1 3000
a2 2000
a3 1000
a2 3000
要求查询总销售额最大的员工姓名跟销售额。我是一个新手,请大家帮帮忙。另外就是请大家推荐一本比较全面点的介绍pl/sql的书,谢谢了。
workername salemoney
a1 3000
a2 2000
a3 1000
a2 3000
要求查询总销售额最大的员工姓名跟销售额。我是一个新手,请大家帮帮忙。另外就是请大家推荐一本比较全面点的介绍pl/sql的书,谢谢了。
from
(
select workername,sum(salemoney) as total,rownum as rn
from sales
group by workername
order by sum(salemoney) desc
)
where rn = 1
存储到临时表中,然后根据临时表中,金额最大的选取需要的数据。
SELECT * FROM (SELECT workername,sum(salemoney) FROM sales GROUP BY workername ORDER BY sum(salemoney) ASC) WHERE ROWNUM=1
我试过,这样是可以满足要求的。
from (select workername, sum(salemoney) as salemoney
from sales
group by workername)
where salemoney = (select max(salemoney)
from (select workername, sum(salemoney) as salemoney
from sales
group by workername)
)
from sale
group by workername