我已经创建了我的表。我正在尝试创建一个查询,将已售表中的 sell_quantity 和 on_sale 表中的 sale_price 相乘并相加,暂时将其称为 R1,将产品表中的 Retail_price 和已售表中的 sell_quantity 暂时称为 R2。
在我的查询中,我想计算我的收入。问题是有两个不同的日期,但有一个销售数量。这意味着我很难区分两种类型的销售(折扣销售、零售销售)。
例如,2月1日,我正在进行促销,我售出了10个数量,售出价格为sale_price,日期保存为sale_date,请参阅On_sale表。 2 月 2 日,我售出 8 件,但售出价格保存为 Retail_price 并保存为 sell_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
table:
store_number phone_number street_address city_name state
1 # ### New York NY
2 # ### HOUSTON TX
3 # ### L.A CA
Sold
Table:
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
Table:
PID SALE_DATE SALE PRICE
1 2/4/2018 2
Product
Table:
PID PRODUCT NAME RETAIL_PRICE manufacture_name
1 XX 5 XXX
2 XX 4 XXX
3 XX 3 XXX
City
table:
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年,收入为(2来自on_sale表的sale_price)+(12(3 * 4,3是sold_table中的sold_quantity,4是retail_price)= 14。
城市大小按范围分隔,其中 0>#=x
这需要我在查询后加入城市表以获得R1(正常价格)和R2(促销价格)。这就是我得到的。我很失落:
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;