如何根据sql server中的产品选择顶级客户？ [英] How can i select top customer based on product in sql server?
问题描述
大家好，
我有1张桌子（tblmix_sales_header_details）。这个表包含发票信息，客户信息和产品信息。我想根据每个产品展示顶级客户.ex：in表有50行，它有4个项目（P1，P2，P3，P4），如果我选择P1的客户信息，我会看到15个客户，对于P2，我会看到10个客户....
我的要求是我想显示商品清单（P1-> P4）并且还显示每个商品的顾客，顾客将是参数顶部。例如：我想展示前五名顾客，所以每件商品（P1-> P4）每个商品都有5个顾客。
那么有谁知道我应该使用什么样的查询？
谢谢
TONY
这是我的例子：
项目客户金额
P1 C1 10
P2 C1 10
P3 C1 10
P1 C2 10
P3 C2 10
P1 C3 10
P2 C3 10
其他项目和客户...........
在此例程中：P1有3个客户，P2有2个客户，P3有2个客户。
如果我选择top 3 =>结果需要显示
项目客户金额
P1
C1 10
C2 10
C3 10
P2
C1 10
C3 10
P3
C1 10
C2 10
和其他项目
谢谢
TONY
要为每个项目获取前5个记录，请尝试以下方法：
DECLARE @ tmp TABLE （Item VARCHAR （ 30 ），客户 VARCHAR （ 30 ），金额 INT ）
- 插入测试数据
DECLARE @ i INT
DECLARE @ j INT
DECLARE @ k INT
DECLARE @ rnd INT
SET @ i = 1
SET @ j = 1
SET @ k = 1
WHILE （@ i< 10） BEGIN
SET @ RND =（（ 15 * RAND（））+ 4 ）
WHILE （@ k< = @ rnd） BEGIN
WHILE （@ j< 10） BEGIN
INSERT INTO @ tmp （项目，客户，金额）
SELECT ' P' + CONVERT （ VARCHAR （ 10 ）， @ i ） AS 项目，' C' + CONVERT （ VARCHAR （ 10 ）， @ j ）AS customer，（@ i * @ rnd + @ j -1） AS 金额
SET @ j = @ j + 1
END
SET @ k = @ k + 1
SET @ j = 1
END
SET @ k = 1
SET @ i = @i + 1
END
- 使用CTE
; WITH CTE_Items AS
（
SELECT DISTINCT 项
FROM @ tmp
），CTE_Customers AS
（
SELECT t2.Item，t1.customer，t1.Amount
FROM < span class =code-sdkkeyword> @ tmp AS t1 INNER JOIN CTE_Items AS t2 ON t1.Item = t2.Item
）
SELECT T. *
FROM （
SELECT ROW_NUMBER（） OVER （ PARTITION BY 项 ORDER BY 项目） AS RowNo，Item，customer，Amount
FROM CTE_Customers
） AS T
WHERE T.RowNo< = 5
ORDER BY T.Item
- TOTAL Count
- SELECT Item，[C1]，[C2]，[C3]，[C4]，[C5]，[C6]，[C7]，[C8]，[C9]
- FROM（
- SELECT Item，customer
- FROM @tmp
-）AS DT
- PIVOT（COUNT（客户）FOR客户IN（[C1]，[C2]，[C3]，[C4]，[C5]，[C6]，[C7]，[C8]，[C9]））AS PT
示例结果：
1 P1 C1 18
2 P1 C2 19
3 P1 C3 20
4 P1 C4 21
5 P1 C5 22
1 P2 C1 16
2 P2 C2 17
3 P2 C3 18
4 P2 C4 19
5 P2 C5 20
1 P3 C1 27
2 P3 C2 28
3 P3 C3 29
4 P3 C4 30
5 P3 C5 31
1 P4 C1 64
2 P4 C2 65
3 P4 C3 66
4 P4 C4 67
5 P4 C5 68
1 P5 C1 20
2 P5 C2 21
3 P5 C3 22
4 P5 C4 23
5 P5 C5 24
1 P6 C1 42
2 P6 C2 43
3 P6 C3 44
4 P6 C4 45
5 P6 C5 46
1 P7 C1 119
2 P7 C2 120
3 P7 C3 121
4 P7 C4 122
5 P7 C5 123
1 P8 C1 56
2 P8 C2 57
3 P8 C3 58
4 P8 C4 59
5 P8 C5 60
1 P9 C1 36
2 P9 C2 37
3 P9 C3 38
4 P9 C4 39
5 P9 C5 40
Hi all experts,
I have 1 table(tblmix_sales_header_details).this table contains of invoice info,customer info and product info.i want to display top customer based on each product.ex: in table has 50 rows and it has 4 items (P1,P2,P3,P4) and if i select customer info for P1, i'll see 15 customer and for P2 i'll see 10 customer ....
My requirement is that i want to display list of item (P1->P4) and also display customer for each item and the customer will be top by parameter. ex: i want to display top 5 customers so each item (P1->P4) will has 5 customer for each item.
So Does anybody know what query should i use?
Thanks
TONY
[EDIT #1]
Here is my example:
Item customer Amount
P1 C1 10
P2 C1 10
P3 C1 10
P1 C2 10
P3 C2 10
P1 C3 10
P2 C3 10
Other Item and customer...........
In this ex:P1 has 3 customers, P2 has 2 customers,P3 has 2 customers.
If i select top 3=> The result need to display
Item Customer Amount
P1
C1 10
C2 10
C3 10
P2
C1 10
C3 10
P3
C1 10
C2 10
and other Item
Thanks
TONY
To fetch top 5 records for eachItem, try this:
DECLARE @tmp TABLE (Item VARCHAR(30), customer VARCHAR(30), Amount INT) --insert testing data DECLARE @i INT DECLARE @j INT DECLARE @k INT DECLARE @rnd INT SET @i = 1 SET @j = 1 SET @k = 1 WHILE (@i<10) BEGIN SET @rnd = ((15 * RAND()) + 4) WHILE (@k<=@rnd) BEGIN WHILE (@j<10) BEGIN INSERT INTO @tmp (Item, customer, Amount) SELECT 'P' + CONVERT(VARCHAR(10),@i) AS Item, 'C' + CONVERT(VARCHAR(10),@j) AS customer, (@i* @rnd +@j -1) AS Amount SET @j = @j+1 END SET @k = @k + 1 SET @j = 1 END SET @k = 1 SET @i = @i + 1 END --using CTE ;WITH CTE_Items AS ( SELECT DISTINCT Item FROM @tmp ), CTE_Customers AS ( SELECT t2.Item, t1.customer, t1.Amount FROM @tmp AS t1 INNER JOIN CTE_Items AS t2 ON t1.Item = t2.Item ) SELECT T.* FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY Item ORDER BY Item) AS RowNo, Item, customer, Amount FROM CTE_Customers ) AS T WHERE T.RowNo<=5 ORDER BY T.Item --TOTAL Count --SELECT Item, [C1],[C2],[C3],[C4],[C5],[C6],[C7],[C8],[C9] --FROM ( -- SELECT Item, customer -- FROM @tmp -- ) AS DT --PIVOT(COUNT(customer) FOR customer IN([C1],[C2],[C3],[C4],[C5],[C6],[C7],[C8],[C9])) AS PT
Example result:
1 P1 C1 18 2 P1 C2 19 3 P1 C3 20 4 P1 C4 21 5 P1 C5 22 1 P2 C1 16 2 P2 C2 17 3 P2 C3 18 4 P2 C4 19 5 P2 C5 20 1 P3 C1 27 2 P3 C2 28 3 P3 C3 29 4 P3 C4 30 5 P3 C5 31 1 P4 C1 64 2 P4 C2 65 3 P4 C3 66 4 P4 C4 67 5 P4 C5 68 1 P5 C1 20 2 P5 C2 21 3 P5 C3 22 4 P5 C4 23 5 P5 C5 24 1 P6 C1 42 2 P6 C2 43 3 P6 C3 44 4 P6 C4 45 5 P6 C5 46 1 P7 C1 119 2 P7 C2 120 3 P7 C3 121 4 P7 C4 122 5 P7 C5 123 1 P8 C1 56 2 P8 C2 57 3 P8 C3 58 4 P8 C4 59 5 P8 C5 60 1 P9 C1 36 2 P9 C2 37 3 P9 C3 38 4 P9 C4 39 5 P9 C5 40
这篇关于如何根据sql server中的产品选择顶级客户？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
