获取连接三个表的汇总平均值,并在第一个表中的每个值旁边显示它们 [英] Get aggregated average values joining three tables and display them next to each value in first table
问题描述
我有三个表,您也可以在> SQL提琴 :
I have three tables which you can also find in the SQL fiddle:
CREATE TABLE Sales (
Product_ID VARCHAR(255),
Sales_Value VARCHAR(255),
Sales_Quantity VARCHAR(255)
);
INSERT INTO Sales
(Product_ID, Sales_Value, Sales_Quantity)
VALUES
("P001", "500", "200"),
("P002", "600", "100"),
("P003", "300", "250"),
("P004", "900", "400"),
("P005", "800", "600"),
("P006", "200", "150"),
("P007", "700", "550");
CREATE TABLE Products (
Product_ID VARCHAR(255),
Product_Name VARCHAR(255),
Category_ID VARCHAR(255)
);
INSERT INTO Products
(Product_ID, Product_Name, Category_ID)
VALUES
("P001", "Shirt", "C001"),
("P002", "Dress", "C001"),
("P003", "Hoodie", "C002"),
("P004", "Ball", "C002"),
("P005", "Ski", "C002"),
("P006", "Boot", "C003"),
("P007", "Flip-Flop", "C003");
CREATE TABLE Categories (
Category_ID VARCHAR(255),
Category_Name VARCHAR(255)
);
INSERT INTO Categories
(Category_ID, Category_Name)
VALUES
("C001", "Fashion"),
("C002", "Sport"),
("C003", "Shoes");
第一个表包含每个产品的Sales
.
第二张表包含有关每个product
的详细信息.
第三个表包含categories
.
The first table contains the Sales
for each product.
The second table contains details about each product
.
The third table contains categories
.
现在,我要显示所有产品以及每个产品旁边的average_sales_price_per_category
.
结果应如下所示:
Now, I want to display all products and the average_sales_price_per_category
next to each product.
The result should look like this:
Product_ID Category average_sales_price_per_category
P001 Fashion 3.66
P002 Fashion 3.66
P003 Sport 1.60
P004 Sport 1.60
P005 Sport 1.60
P006 Shoes 1.28
P007 Shoes 1.28
I tried to go with the solution from this question but I get an Error
:
SELECT s.Product_ID, c.Category_Name,
(SELECT SUM(SS.Sales_Value) / SUM(SS.Sales_Quantity)
FROM Sales SS
WHERE SS.Category_ID = S.Category_ID
) AS average_sales_price
FROM Sales s
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN Categories c ON c.Category_ID = p.Category_ID;
错误
Unknown column 'SS.Category_ID' in 'where clause'
我需要在代码中进行哪些更改才能获得预期的结果?
What do I need to change in my code to get the expected result?
推荐答案
您的表在内部子查询中不可见,以避免内部子查询中的where条件可以在组合的子查询中使用联接
You s table is not visible in inner subquery for avoid the where condition in inner subquery you could use a join on the grouped aggreated subquery
SELECT
s.Product_ID,
Price_Category.average_sales_price_per_category
FROM Sales s
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN
(SELECT
c.Category_ID,
c.Category_Name,
SUM(s.Sales_Value) / SUM(s.Sales_Quantity) AS average_sales_price_per_category
FROM Sales s
JOIN Products p ON p.Product_ID = s.Product_ID
JOIN Categories c ON c.Category_ID = p.Category_ID
GROUP BY 1) Price_Category ON Price_Category.Category_ID = p.Category_ID;
SQL Fiddle
这篇关于获取连接三个表的汇总平均值,并在第一个表中的每个值旁边显示它们的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!