从SQL Count查询中选择顶部结果，包括表Join -Oracle

Question

我当前有此查询，该查询以降序选择顶部的"拾取数"。我只需要过滤前10行/最高数字。我怎样才能做到这一点？

我尝试在底部添加'rownum＆lt; = 10'，无济于事。

SELECT customer.company_name, COUNT (item.pickup_reference) as "Number of Pickups" 
FROM customer
JOIN item ON (customer.reference_no=item.pickup_reference) 
GROUP BY customer.company_name, item.pickup_reference
ORDER BY COUNT (customer.company_name) DESC;

感谢您的任何帮助！

Answer 1

您需要对其进行划分以使Rownum工作。

SELECT *
FROM
(
SELECT customer.company_name, COUNT (item.pickup_reference) as "Number of Pickups" 
FROM customer
JOIN item ON (customer.reference_no=item.pickup_reference) 
GROUP BY customer.company_name, item.pickup_reference
ORDER BY COUNT (customer.company_name) DESC
) 
WHERE rownum <= 10

您可以使用排名函数，但是考虑到这一点的相对简单性，我不确定是否会。

Answer 2

使用等级的解决方案是这样的：

select customer.company_name, COUNT (item.pickup_reference) from (
    select distinct customer.company_name, COUNT (item.pickup_reference) ,
    rank() over ( order by count(item.pickup_reference) desc) rnk 
    from customer
    JOIN item ON (customer.reference_no=item.pickup_reference) 
    group by customer.company_name, item.pickup_reference
    order by COUNT (customer.company_name) )
where rnk < 10

使用" Rownum"获得最高结果并没有给出预期的结果，因为它获得了10行的10行，然后订购它们t有权添加评论）。