我如何...没有得到特定客户购买的产品 [英] 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屋！