我已经创建了表格.我正在尝试创建一个查询,该查询将已售表中的sold_quantity乘以on_sale表中的sale_price并相加,现在称为R1,而产品表中的Retail_price和已售表中的sold_quantity称为R2.
在查询中,我要计算我的收入.收获是有两个不同的日期,但有一个销售数量.这意味着我很难区分两种销售类型(折扣销售,零售销售).
例如,在2月1日,我正在进行一次销售,销售了10个数量,出售的价格为sale_price,日期保存为sale_date,请参阅On_sale表.在2月2日,我卖出了8个数量,但是卖出的价格保存为retail_price并保存为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,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,population int(16) NOT NULL DEFAULT '0',PRIMARY KEY (city_name,state)
);
这就是我要的：
样本数据：
店铺表：
store_number phone_number street_address city_name state
1 # ### New York NY
2 # ### HOUSTON TX
3 # ### L.A CA
出售表：
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
特价表：
PID SALE_DATE SALE PRICE
1 2/4/2018 2
产品表：
PID PRODUCT NAME RETAIL_PRICE manufacture_name
1 XX 5 XXX
2 XX 4 XXX
3 XX 3 XXX
城市表：
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.
城市大小由范围分隔,其中0≤100较小100 ＝ x≤200中等,大于200的任何事物都较大.括号中的城市名称仅用于帮助跟踪.城市基于商店表中的城市名称和州,通过比较已售表和商店表上的store_number来确定城市
这要求我在查询后加入城市表以获取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;
SELECT
YEAR(sale_date) yr,SUM(sale_price) amt
FROM
On_sale
GROUP BY
YEAR(sale_date);
此其他查询使用“已售出”和“产品”表获取每家商店和每年的收入：
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);
现在,我们可以使用“城市”和“商店”表联接这些查询的结果.同时,我们可以将城市划分为不同的大小类别,并使用它们来汇总结果.我正在使用LEFT JOIN,以防子查询之一产生空结果集(否则,INNER JOIN可以)：
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,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
带有示例数据的demo on DB Fiddle演示了中间步骤,最后返回：
| sale_year | size_range | revenue |
| --------- | ---------- | ------- |
| 2017 | small | 15 |
| 2018 | medium | 14 |
| 2019 | large | 12 |