使用5个表格生成查询 [英] Generate query using 5 tables
问题描述
我已经创建了表格.我正在尝试创建一个查询，该查询将已售表中的sold_quantity乘以on_sale表中的sale_price并将其添加为R1，现在将产品表中的Retail_price和已售表中的sold_quantity称为R2.
I've created my table. I'm trying to craate a query that multiply and add sold_quantity from sold table and sale_price from on_sale table and called it R1 for now, and retail_price from product table and sold_quantity from sold table called it R2 for now.
在我的查询中，我想计算我的收入.收获是有两个不同的日期，但有一个销售数量.这意味着我很难区分两种销售类型(折扣销售，零售销售).
In my query, I want to calculate my revenue. The catch is there's two different date but one sale quantity. That means it's kinda hard for me to distinguish twO types of sales( discounted sale, retail sale).
例如，在2月1日，我正在进行一次销售，我出售了10个数量，出售的价格为sale_price，日期保存为sale_date，请参阅On_sale表.在2月2日，我卖出了8个数量，但是卖出的价格被保存为retail_price并保存为sold_date.
For example, on Feb.1st, I have a sale going on, I sold 10 quantity, and price sold is as sale_price and date is saved as sale_date, refer to On_sale table. On Feb.2, I sold 8 quantity , but price sold is saved as retail_price and saved as sold_date.
CREATE TABLE Sold (
store_number int(16) NOT NULL AUTO_INCREMENT,
pid int(16) NOT NULL,
sold_date date NOT NULL,
sold_quantity int(16) NOT NULL,
PRIMARY KEY (pid,store_number,sold_date)
);
CREATE TABLE Store (
store_number int(16) NOT NULL AUTO_INCREMENT,
phone_number varchar(16) NOT NULL DEFAULT '0',
street_address varchar(250) NOT NULL,
city_name varchar(250) NOT NULL,
state varchar(250) NOT NULL,
PRIMARY KEY (store_number)
);
CREATE TABLE On_sale (
pid int(16) NOT NULL,
sale_date date NOT NULL,
sale_price float(16) NOT NULL,
PRIMARY KEY (pid,sale_date)
);
CREATE TABLE Product (
pid int(16) NOT NULL,
product_name varchar(250) NOT NULL,
retail_price float(16) NOT NULL,
manufacture_name varchar(250) NOT NULL,
PRIMARY KEY (pid)
);
CREATE TABLE City (
city_name varchar(250) NOT NULL,
state varchar(250) NOT NULL,
population int(16) NOT NULL DEFAULT '0',
PRIMARY KEY (city_name,state)
);
这就是我想要的:
样品数据:
Store表:
store_number phone_number street_address city_name state
1 # ### New York NY
2 # ### HOUSTON TX
3 # ### L.A CA
Sold表格:
store_number PID SOLD DATE SOLD_QUANTITY
1 1 2/2/2017 3
2 2 2/3/2018 3
3 3 2/5/2019 4
On_sale表格:
PID SALE_DATE SALE PRICE
1 2/4/2018 2
Product表格:
PID PRODUCT NAME RETAIL_PRICE manufacture_name
1 XX 5 XXX
2 XX 4 XXX
3 XX 3 XXX
City表:
CITY_NAME STATE POPULATION
New York NY 100
HOUSTON TX 200
L.A CA 201
扩展结果:
YEAR REVENUE POPULATION
2017 15 (NEW YORK)SMALL
2018 14 (HOUSTON)MEDIUM
2019 12 (L.A) LARGE
我的数据说明
这非常令人困惑.首先，我需要根据销售日期和销售日期显示年份，然后计算收入.例如，在2018年，收入是(on_sale表的sale_price中的2)+(12(3 * 4，3是sold_table中的sold_quantity，4是retail_price)=14.
This is very confusing. First I need to display year based on sold date and sale date, then calculate revenue. For example,in year 2018, the revneue is (2 from on_sale table's sale_price) + (12 (3 * 4, 3 is the sold_quantity from sold_table, and 4 is retail_price) = 14.
城市规模由范围分隔，其中0>#< 100较小100> = x< 200中等，大于200的任何事物都较大.括号中的城市名称仅用于帮助跟踪.城市是根据商店表中的城市名称和州来确定的，并且可以通过比较出售表和商店表上的store_number来确定城市
The city size is separated by ranges, where 0>#<100 is small 100>=x<200 is medium and anything above 200 is large. the city name in the parenthesis is just to help track. The city is based on the city name and state in store table, and that is doen by comparing store_number on both sold table and store table
这要求我在查询后加入城市表以获取R1(正常价格)和R2(销售价格).这就是我得到的.我很迷路:
This requires me to join city table after querying to get R1(normal price) and R2(on sale price). Here's what I got. I'm very lost:
SELECT year(s.sold_date) as yr, c.population,
SUM(COALESCE(os.sale_price, p.retail_price) * s.sold_quantity) AS revenue,
CASE
WHEN population >= 0 AND population < 3700000 THEN 'small'
WHEN population >= 3700000 AND population < 6700000 THEN 'medium'
WHEN population >= 6700000 AND population < 9000000 THEN 'large'
WHEN population >= 9000000 THEN 'extra_large'
ELSE '-1'
END AS cityCategory
FROM Sold s JOIN
Product p
ON s.pid = p.pid JOIN
Store st
ON st.store_number = s.store_number LEFT JOIN
On_sale os
ON s.pid = os.pid JOIN
city c
ON c.city_name = st.city_name
GROUP BY year(s.sold_date), c.population
ORDER BY year(s.sold_date) ASC, c.population;
推荐答案
这是一种可以完成工作的方法.逻辑是使用聚合子查询进行中间计算.
Here is an approach that might get the job done. The logic is to use aggregate subqueries to do the intermediate computations.
此查询按年从On_sale表中获取收入.
This query gets the revenue from the On_sale table by year.
SELECT
YEAR(sale_date) yr,
SUM(sale_price) amt
FROM
On_sale
GROUP BY
YEAR(sale_date);
此其他查询使用表Sold和Product获取每个商店和每年的收入:
This other query gets the revenue per store and per year, using tables Sold and Product:
SELECT
s.store_number,
YEAR(s.sold_date) yr,
SUM(s.sold_quantity * p.retail_price) amt
FROM
Sold s
INNER JOIN Product p
ON p.pid = s.pid
GROUP BY
s.store_number,
YEAR(sold_date);
现在，我们可以使用City和Store表JOIN这些查询的结果.同时，我们可以将城市划分为不同的大小类别，并使用它们来汇总结果.我正在使用LEFT JOIN，以防子查询之一产生空结果集(否则，INNER JOIN可以):
Now we can JOIN the results of these queries with the City and Store tables. At the same time, we can split the cities into different size categories and use that to aggregate the results. I am using LEFT JOIN in case one of the subqueries produces an empty resultset (else, INNER JOIN is ok):
SELECT
COALESCE(sa.yr, so.yr) sale_year,
CASE
WHEN c.population > 200 THEN 'large'
WHEN c.population <= 200 AND c.population > 100 THEN 'medium'
ELSE 'small'
END as size_range,
SUM(COALESCE(so.amt, 0) + COALESCE(sa.amt, 0)) revenue
FROM
City c
INNER JOIN Store st
ON st.state = c.state
AND st.city_name = c.city_name
LEFT JOIN (
SELECT
s.store_number,
YEAR(s.sold_date) yr,
SUM(s.sold_quantity * p.retail_price) amt
FROM
Sold s
INNER JOIN Product p
ON p.pid = s.pid
GROUP BY
s.store_number,
YEAR(sold_date)
) so
ON so.store_number = st.store_number
LEFT JOIN (
SELECT
YEAR(sale_date) yr,
SUM(sale_price) amt
FROM
On_sale
GROUP BY
YEAR(sale_date)
) sa
ON sa.yr = so.yr
GROUP BY
sale_year,
size_range
ORDER BY
sale_year,
size_range
此 DB Fiddle上的演示 您的样本数据演示了中间步骤，并最终返回:
This demo on DB Fiddle with your sample data demonstrates the intermediate steps, and finally returns:
| sale_year | size_range | revenue |
| --------- | ---------- | ------- |
| 2017 | small | 15 |
| 2018 | medium | 14 |
| 2019 | large | 12 |
这篇关于使用5个表格生成查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
