SQL 解决 QUERY 以显示 TOP 5 畅销产品
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13600110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Solve QUERY for showing TOP 5 selling Products
提问by Usman YousafZai
I have a table of Customer_Invoicehaving columns of item_nameand Quantity. I want to show the top 5 Items on the specific item & Item Quantity Sold in Descending order i-e if the number of item A is sold 5 and the number of item B is sold 3 then Item A should be on Top and and item B will be on 2nd number.
我有一个表Customer_Invoice具有列item_name和Quantity。我想显示特定项目的前 5 个项目和按降序销售的项目数量，即如果项目 A 的数量是 5，项目 B 的数量是 3，那么项目 A 应该在顶部，项目 B 将排在第二位。
| ITEMCODE | QUANTITY |
------------------------
| kb434 | 1 |
| A4tech123 | 4 |
| HDD40GB | 4 |
| Cell12 | 4 |
| Icd123 | 2 |
| A4tech123 | 6 |
In the above diagram I want A4tech123on 1st no HDD40GBon 2nd No, Cell12on third no and so on.
在上图中，我想要A4tech123第一个没有HDD40GB，第二个没有，Cell12第三个没有，依此类推。
回答by mcalex
select top 5 Item_code, sum(Quantity)
from customer_invoice
group by Item_code
Order by sum(Quantity) desc
回答by Pranay Rana
select top 5 item_name , sum(Quantity) as Quantity from Customer_Invoice
group by item_name
ORDER BY sum(Quantity) DESC
回答by Gokul Potluri
If you are using the postgres as DB then use the below query:
如果您使用 postgres 作为数据库，请使用以下查询：
select Item_code, Quantity from customer_invoice order by Quantity desc limit 5
OR
或者
if you using any other DB use this query:
如果您使用任何其他数据库，请使用此查询：
select top 5 Item_code, Quantity from customer_invoice order by Quantity desc
i didn't checked the second as i am using Postgres DB, so before using confirm that query
我没有检查第二个，因为我使用的是 Postgres DB，所以在使用之前确认该查询
回答by Saloni Ajmera
CREATE TABLE invoice
(ITEMCODE varchar(9), QUANTITY int)
;
INSERT INTO invoice
VALUES
('kb434', 1),
('A4tech123', 4),
('HDD40GB', 4),
('Cell12', 4),
('Icd123', 2),`
('A4tech123', 6)
;
SELECT ITEMCODE,
SUM(QUANTITY) as total_quantity,
ROW_NUMBER() OVER (order by sum(QUANTITY) desc) as rank_n
FROM invoice
GROUP BY ITEMCODE
回答by hims056
Try this one:
试试这个：
SELECT * FROM
(
SELECT ITEMCODE, SUM(QUANTITY) "TotalQty"
FROM MyTable
GROUP BY ITEMCODE
ORDER BY SUM(QUANTITY) DESC
, ITEMCODE ASC
) A
WHERE rownum <= 5;
Here you need to use sub-query because without using sub-query it will not group all ITEMCODElike this(because we are using WHERE rownum <=5). So you need to add sub-query.
在这里你需要使用子查询，因为不使用子查询它不会ITEMCODE像这样分组（因为我们正在使用WHERE rownum <=5）。所以你需要添加子查询。
See this SQLFiddle
看到这个 SQLFiddle
回答by MANG KANOR
SELECT TOP 5 * FROM Customer_Invoice ORDER BY QUANTITY
