mysql-行使5个表天生查询

我已经建设了表格.我正在实行建设一个查询,该查询将已售表中的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;

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

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演示了中间步调,最后返回：

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!