mysql-使用5个表生成查询

概述我已经创建了表格.我正在尝试创建一个查询,该查询将已售表中的sold_quantity乘以on_sale表中的sale_price并相加,现在称为R1,而产品表中的Retail_price和已售表中的sold_quantity称为R2.在查询中,我要计算我的收入.收获是有两个不同的日期,但有一个销售数量.这意味着我很难区分两种销售类型(折扣销售,零售销售).

我已经创建了表格.我正在尝试创建一个查询,该查询将已售表中的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_name1       XX           5              XXX          2      XX          4             XXX       3       XX           3              XXX       

城市表：

CITY_name  STATE    POPulATION  New York   NY    100HOUSTON    TX    200L.A        CA    201

扩展结果：

YEAR  REVENUE   POPulATION2017   15       (NEW YORK)SMALL 2018   14       (HOUSTON)MEDIUM2019   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 citycategoryFROM 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_nameGROUP BY year(s.sold_date),c.populationORDER BY year(s.sold_date) ASC,c.population;

最佳答案这是一种可以完成工作的方法.逻辑是使用聚合子查询进行中间计算.

该查询按年份从On_sale表中获取收入.

SELECT     YEAR(sale_date) yr,SUM(sale_price) amtFROM     On_saleGROUP BY     YEAR(sale_date);

此其他查询使用“已售出”和“产品”表获取每家商店和每年的收入：

SELECT     s.store_number,YEAR(s.sold_date) yr,SUM(s.sold_quantity * p.retail_price) amtFROM     Sold s    INNER JOIN Product p         ON p.pID = s.pIDGROUP 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)) revenueFROM     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.yrGROUP BY    sale_year,size_rangeORDER BY    sale_year,size_range

带有示例数据的demo on DB Fiddle演示了中间步骤,最后返回：

| sale_year | size_range | revenue || --------- | ---------- | ------- || 2017      | small      | 15      || 2018      | medium     | 14      || 2019      | large      | 12      |

总结

以上是内存溢出为你收集整理的mysql-使用5个表生成查询 全部内容，希望文章能够帮你解决mysql-使用5个表生成查询 所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。