我如何...没有得到特定客户购买的产品 [英] How do I...get no of products bought by a specific customer
问题描述
我有两张桌子,顾客和销售表
栏是
客户
cust_no custname status city
's1''莫里斯'A''加尔各答'
's2''Lewis'C''海得拉巴'
's3''Robin'B''加尔各答'
's4''Rosy'A''Banglore'
's5''Cherian'A'''Chennai'
's6'' Mariam'C''Chennai'
销售
prd_no cust_no qty amt
'p2''s1' 150 0
'p2''s1'150 0
'p1''s1'120 0
'p1''s1'120 0
'p1''s1'120 0
'p6''s1'60 0
'p5''s2'120 0
'p4''s2'130 0
'p4''s3'1 20 0
'p4''s4'120 0
'p2''s4'150 0
'p5''s4'70 0
i想要特定客户购买的产品,是什么问题
这可能会对你有所帮助:http://www.w3schools.com/sql/sql_func_count.asp
如下所示
< pre lang =sql> 选择 c.cust_no,Sum(s.qty)来自
customer c
join sales s on c.cust_no = s.cust_no
group by c.cust_no
ref:
SQL联接 [ ^ ]
SQL SUM()with group by [ ^ ]
试试这个!!
选择 Prd_No,Custname,Count(Prd_No)来自销售内部 加入客户开启
Sales.Cust_no = Customer.Cust_No 其中 Cust_No = ' s1按客户名分组,prd_no
I have two tables , customer and sales table
columns are
customer
cust_no custname status city
's1 ' 'Morris 'A' 'Kolkata '
's2 ' 'Lewis 'C' 'Hyderabad '
's3 ' 'Robin 'B' 'Kolkata '
's4 ' 'Rosy 'A' 'Banglore '
's5 ' 'Cherian 'A' 'Chennai '
's6 ' 'Mariam 'C' 'Chennai '
sales
prd_no cust_no qty amt
'p2 ' 's1 ' 150 0
'p2 ' 's1 ' 150 0
'p1 ' 's1 ' 120 0
'p1 ' 's1 ' 120 0
'p1 ' 's1 ' 120 0
'p6 ' 's1 ' 60 0
'p5 ' 's2 ' 120 0
'p4 ' 's2 ' 130 0
'p4 ' 's3 ' 120 0
'p4 ' 's4 ' 120 0
'p2 ' 's4 ' 150 0
'p5 ' 's4 ' 70 0
i want the no of products bought by a specific customer , whats the query to do so
This might help you: http://www.w3schools.com/sql/sql_func_count.asp
like below
select c.cust_no, Sum(s.qty) from customer c join sales s on c.cust_no = s.cust_no group by c.cust_no
ref:
SQL Joins[^]
SQL SUM() with group by[^]
Try this!!
Select Prd_No, Custname, Count(Prd_No) From Sales Inner Join Customer On Sales.Cust_no = Customer.Cust_No Where Cust_No='s1" Group By Custname, prd_no
这篇关于我如何...没有得到特定客户购买的产品的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!