如何使用SQL SERVER显示前3个最高订购产品和总数量的列表 [英] How to show the list of first 3 highest ordered product and the total quantity using SQL SERVER
问题描述
嗨专家..
我想显示前3个最高订购产品和总数量的清单。
我的表格包含
Hi experts..
I want to display the list of first 3 highest ordered product and the total quantity.
My table consist of
Customer Id Order_Date Item Quantity Price
1 30-Jun-2013 Pogo stick 1 28
2 30-Jan-2013 Raft 1 58
2 2-Feb-2013 Tent 1 99
3 5-Dec-2013 Pen 2 5
4 15-Oct-2013 Tent 1 98
5 5-Jan-2013 Paper 10 15
6 9-Mar-2013 Mobile 1 1500
7 26-Apr-2013 Shirt 7 150
8 27-Apr-2013 Pant 1 450
9 18-Jun-2013 Shoe 5 690
10 18-Nov-2013 Tent 3 300
3 4-Jan-2013 Laptop 1 10000
4 14-Dec-2013 Ring 1 100
5 14-Dec-2013 Pen 3 60
6 21-Dec-2013 Shoe 1 500
我试过这个查询..但我没有得到确切的输出。 $>
1）从Items_Order Group中选择项目，COUNT（数量）AS TOTAL_ORDER按项目COUNT（数量）> 1个按COUNT（数量）desc排序;
2）从Items_Order Group中选择项目，COUNT（数量）AS TOTAL_ORDER按项目顺序排列COUNT（数量）desc;
3）选择项目，COUNT（数量）AS TOTAL_ORDER，SUM（价格）AS Total_Price from Items_Order Group按项目顺序COUNT（数量）desc;
4 ）选择前3 * FROM（选择项目作为项目，COUNT（数量）AS TOTAL_ORDER来自Items_Order Group按项目有COUNT（数量）> 1）AS ord;
5）选择前3 * FROM（从Items_Order组中选择项目AS项目，COUNT（数量）AS TOTAL_ORDER按项目顺序COUNT（数量）desc）AS订购;
6）选择前3 * FROM（选择项目，COUNT（数量）AS TOTAL_ORDER，SUM（pric） e）来自Items_Order Group的AS Total_Price按项目顺序COUNT（数量）desc）AS订购;
1,2,3和第四是工作。但我没有得到确切的输出。
5和6不起作用。它显示了一些错误。
错误信息是：
I have tried this queries.. But i did not get exact output.
1) Select Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item having COUNT(quantity)>1 order by COUNT(quantity) desc;
2) Select Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc;
3) Select Item,COUNT(quantity) AS TOTAL_ORDER,SUM(price) AS Total_Price from Items_Order Group By item order by COUNT(quantity) desc;
4) select top 3 * FROM (Select Item AS ITEM,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item having COUNT(quantity)>1) AS ord;
5) select top 3 * FROM (Select Item AS ITEM,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc) AS ordered;
6) select top 3 * FROM (Select Item,COUNT(quantity) AS TOTAL_ORDER,SUM(price) AS Total_Price from Items_Order Group By item order by COUNT(quantity) desc) AS ordered;
1,2,3 and 4th is working. But I did not get the exact output.
5 and 6 is not working. It show some error.
The error message is:
Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
如何使用子查询来获取输出。请帮帮我..
How to use sub query's to get the output. Please help me..
推荐答案
这个怎么样..
How about this..
Select Top 3 Item,COUNT(quantity) AS TOTAL_ORDER from Items_Order Group By item order by COUNT(quantity) desc;
另外，您想要总数量..
Also, you want total quantity..
SELECT TOP 3 item, Count(*) as Total_Order, Sum(quantity) as Total_Quantity
FROM Items_Order
Group By item
Order By Count(*) desc;
这篇关于如何使用SQL SERVER显示前3个最高订购产品和总数量的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
