mySQL 创建一个复杂的查询/ View /报告

Question

我有如下 3 个表:

1/表'order_fruit'

-------------------------------------------------------------
id_fruit_table | fruit_name | order_quantity | delivery_date
---------------+------------+----------------+---------------
1              |mango       | 10             | 04 2018
2              |mango       | 5              | 05 2018
3              |banana      | 20             | 04 2018
4              |pineapple   | 9              | 06 2018

2/表 'stock_fruit'

---------------------------------------------------
id_stock      | fruit_name_stock  | stock_quantity 
--------------+-------------------+----------------
1             | mango             | 5
2             | pineapple         | 10

3/表'管道'

--------------------------------------------------------------------
id_pipeline   | fruit_pipeline   | receive_date | pipeline_quantity
--------------+------------------+--------------+-------------------
1             | mango            | 04 2018      | 5
2.            | banana           | 05 2018      | 15 

我如何进行如下查询/查看/报告:

-----------------------------------------------------------------------------------
fruit    |    04 2018                 | 05 2018                          | 06 2018 
---------+---------------------------------------------------------------+---------
         | order_qty-| stock-| pipeline | order_qty- | stock- | pipeline | ...
---------+-----------+-------+----------+------------+--------+----------+---------
mango    | 10        | 5     | 5        | 5          | 0      | 0        | ...
banana   | 20        | 0     | 0        | 0          | 0      | 15       | ...
pineapple| 0         | 10    | 0        | 0          | 10     | 0        | ...

此处为 05 2018 = 04 2018(库存 + 管道 - order_qty)

06 2018 = 05 2018(库存 + 管道 - order_qty)的期初库存

Answer 1

尝试使用下面的方式

-- the first query returns all distinct dates
SELECT delivery_date oper_date
FROM order_fruit

UNION

SELECT receive_date
FROM pipeline

并且您可以使用此信息生成第二个查询

-- the second query is generated using information about dates from the first query
SELECT
  fruit_name
  ,SUM(stock_quantity) stock_quantity

  -- 04 2018
  ,SUM(CASE WHEN oper_date='04 2018' THEN  order_quantity END) order_quantity_04_2018
  ,SUM(CASE WHEN oper_date='04 2018' THEN  pipeline_quantity END) pipeline_quantity_04_2018

  -- 05 2018
  ,SUM(CASE WHEN oper_date='05 2018' THEN  order_quantity END) order_quantity_05_2018
  ,SUM(CASE WHEN oper_date='05 2018' THEN  pipeline_quantity END) pipeline_quantity_05_2018

  -- 06 2018
  ,SUM(CASE WHEN oper_date='06 2018' THEN  order_quantity END) order_quantity_06_2018
  ,SUM(CASE WHEN oper_date='06 2018' THEN  pipeline_quantity END) pipeline_quantity_06_2018

  -- ...
FROM
  (
    SELECT delivery_date oper_date,fruit_name,order_quantity,NULL stock_quantity,NULL pipeline_quantity
    FROM order_fruit

    UNION ALL

    SELECT NULL,fruit_name_stock,NULL,stock_quantity,NULL
    FROM stock_fruit

    UNION ALL

    SELECT receive_date,fruit_pipeline,NULL,NULL,pipeline_quantity
    FROM pipeline
  ) q
GROUP BY fruit_name

SQL fiddle -http://www.sqlfiddle.com/#!9/ceb1680/2