Sql语句将显示每个客户正在购买的所有产品 [英] Sql statement that will display all of the products that each customer is buying

问题描述

我有客户表和产品表。

客户表 列 :( c_id，f_name，l_name，email， group ，state，c_id 是  primary   key ）
 
产品表列：（p_id，c_id，p_name，p_desc，时间，c_id    foreign   key ）。

我想知道如何得到以下问题的结果：



1）显示每个客户购买的所有产品。



2）使用问题（1）显示来自此查询的每个州的客户数量。



我是刚进入sql并开始自学。我不知道天气我是正确的以下查询。请帮帮我。

感谢您的帮助。



我的尝试：



答案：



第一个问题答案：

  SELECT   DISTINCT  p_name  FROM  PRODUCT; 

第二个问题答案：

  SELECT  A.state，B.p_name，count（A.state） AS 总计
  FROM  CUSTOMER  AS  A 
  INNER   JOIN  PRODUCT  AS  B 
  ON  A.c_id = B.c_id < span class =code-keyword> GROUP   BY  STATE; 

解决方案

1。显示每个客户购买的所有产品。



*我假设您想要获得每个客户购买的所有产品的清单。

  SELECT  c_id，p_name 
  FROM  PRODUCT 
  GROUP   BY  c_id 

2.使用问题（1）显示来自此查询的每个州的客户数量。

  SELECT  p.p_name，c.state 
  FROM  PRODUCT p  JOIN  CUSTOMER c 
  ON  p.c_id = c.c_id 

无论如何，表格不符合规范化。

您应该将3个表格分开，即客户，产品和订单。不要将产品和订单存储在1个表中。因为如果你删除了产品，那么订单的数据也会消失。



希望这会有所帮助。

引用：

我不知道天气我对下面的查询是正确的。

有一种简单的方法可以知道查询是否正确：只需试一试，您将看到答案是否符合预期。

试验是一种很好的方式学习。要测试查询，首先设置反映其中一个情况的数据，然后启动查询并检查答案，然后再次使用下一个数据集。



这里是一个很棒的教程网站

SQL教程 [ ^ ]

I have customer table and product table.

Customer table column : (c_id,f_name,l_name,email,group,state, c_id is primary key)

Product Table column: (p_id,c_id,p_name,p_desc,time,c_id is foreign key).

I want to know how to get the result for below question:

1)display all of the products that each customer is buying.

2)Using question(1) display how many customers are from each state from this query.

I am just getting into sql and started learning myself.I dont know weather I am correct with the below queries. Please do help me.
Thanks for the help.

What I have tried:

Answer:

1st question answer:

SELECT DISTINCT p_name FROM PRODUCT;

2nd question answer :

SELECT A.state,B.p_name,count(A.state) AS Total 
FROM CUSTOMER AS A 
INNER JOIN PRODUCT AS B 
ON A.c_id=B.c_id GROUP BY STATE;

解决方案

1. Display all of the products that each customer is buying.

*I assume that you want get the list of all the product that bought by each customer.

SELECT c_id, p_name
FROM PRODUCT
GROUP BY c_id

2. Using question(1) display how many customers are from each state from this query.

SELECT p.p_name, c.state
FROM PRODUCT p JOIN CUSTOMER c
ON p.c_id = c.c_id

Anyway, the tables don't fulfill the Normalization.
You should separate the into 3 tables, which are Customers, Products and Orders. Don't store the Products and the Orders into 1 table. Because if you delete the product, then the Orders' data will be gone too.

Hope this helps.

Quote:

I don't know weather I am correct with the below queries.

There is an easy way to know if a query is correct or not: Just give it a try and you will see if the answer is as expected or not.
Experimenting is a great way to learn. To test a query, start by setting data that reflect 1 of the situations to handle then launch the query and check the answer, then start again with next dataset.

Here is a great tutorial site
SQL Tutorial[^]

这篇关于Sql语句将显示每个客户正在购买的所有产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！