mysql - SQL 查询求和、乘法和组合 5 个表然后分组

Question

我已经创建了我的表。我正在尝试创建一个查询，将 sold 表中的 sold_quantity 和 on_sale 表中的 sale_price 相乘并相加，现在将其称为 R1，将 product 表中的 retail_price 和 sold 表中的 sold_quantity 暂时称为 R2。
在我的查询中，我想计算我的收入。问题是有两个不同的日期，但有一个销售数量。这意味着我很难区分两种类型的销售(折扣销售、零售销售)。
比如2月1日我有一个sale，我卖了10个，sale_price是sale_price，date是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,
  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 TABLES
store_number  phone_number  street_address city_name state 
     1           #             ###          New York    NY
     2           #             ###          HOUSTON     TX
     3           #             ###          L.A         CA


Sold Tables
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 Tables
PID  SALE_DATE    SALE PRICE  
1      2/4/2018    2

Product Tables
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

RESULT 

YEAR  REVENUE POPULATION
2017   15       (NEW YORK)SMALL 
2018   14        (HOUSTON)MEDIUM
2019   12       (L.A) LARGE

我的数据说明
:这非常令人困惑。首先我需要根据销售日期和销售日期显示年份，然后计算收入。例如2018年revneue为(2 from on_sale table's sale_price) + (12 (3 * 4, 3为sold_quantity from sold_table, 4为retail_price) = 14。
城市大小按范围分隔，其中 0>#<100 为小，100>=x<200 为中，大于 200 的为大。括号中的城市名称只是为了帮助跟踪。城市基于商店表中的城市名称和州，这是通过比较已售出表和商店表上的 store_number 来完成的
这需要我在查询得到 R1(正常价格)和 R2(特价)后加入城市表。这是我得到的。我很迷茫

SELECT Sold.sold_quantity,
       Product.retail_price AS S1
    SUM(Product.retail_price*Sold.sold_quantity) AS R1

FROM  Sold LEFT JOIN Product
ON     Sold.pid=Product.pid

SELECT Sold.sold_quantity,
       On_sale.sale_price AS S2
    SUM(On_sale.sale_price) AS R2

FROM  Sold LEFT JOIN On_sale
ON     Sold.pid=On_sale.pid

Answer 1

你可以试试下面-

SELECT year(sold_date) as yr,population
,SUM(Product.retail_price*Sold.sold_quantity) -SUM(On_sale.sale_price * Sold.sold_quantity) AS revenue
FROM  Sold 
LEFT JOIN Product ON  Sold.pid=Product.pid
LEFT JOIN On_sale ON Sold.pid=On_sale.pid
left join city on city.city_name=product.manufacture_name 
group by year(sold_date),population