mysql创建复杂的查询/视图/报表

ezykj2lf 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(409)

我有三张table如下：
1/桌“订购水果”

-------------------------------------------------------------
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/桌“库存水果”

---------------------------------------------------
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(stock + pipeline - order_qty) 期初库存 06 2018 = 05 2018(stock + pipeline - order_qty)

mysql

来源：https://stackoverflow.com/questions/49748724/mysql-creating-a-complex-query-view-report

1条答案

按热度按时间

nnsrf1az1#

试着用下面的方法

-- 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小提琴-http://www.sqlfiddle.com/#!9/ceb1680/2号

赞(0）回复(0）举报 2021-06-24

我来回答

mysql创建复杂的查询/视图/报表

1条答案

相关问题

热门标签

最新问答