时间戳

首页 > 解决方案 > 在mysql中加入5个表，在同一个查询中显示两个不同表的两个SUM列

mysql - 在mysql中加入5个表，在同一个查询中显示两个不同表的两个SUM列

问题描述

我正在一个项目中工作，以控制用于不同任务的物品库存。每个任务都有一个或多个仓库，每个仓库接收完成任务所需的物品的输入，最后用于任务的真实物品数量在used_items表中。

表格

task:
taskId  | name 
-----------------

warehouse: 
warehous_id  | taskId
----------------------

inputs:
itemId |  qty  |  warehouse_id
--------------------------------


items:
itemId  | itemName
--------------------


used:
itemId |  qty   |  taskId
-----------------------------

我正在尝试使用以下查询获取特定任务项目的报告：

QUERY1：在特定任务中使用的项目（例如任务 1）：

SELECT t.taskId
     , it.itemId
     , it.itemName
     , SUM(u.qty) total_used 
  FROM items it
     , task t
     , used u
 WHERE it.itemId = u.itemId 
   AND t.taskId = u.taskId 
   AND t.taskId = 1
 GROUP 
    BY it.itemId

此查询给出以下结果：

taskId itemId itemName total_used
1       2       item2     10
1       3       item3      1
1       4       item4      6
1       5       item5      6
1       6       item6      2
1       8       item8      9
1       9       item9      4
1      10       item10     7

这个结果是正确的。使用了 8 个项目。

QUERY2：为特定任务输入项目（例如任务 1）：

SELECT t.taskId
     , it.itemId
     , it.itemName
     , SUM(ip.qty) total_input
  FROM items it
     , task t
     , inputs ip
     , warehouse w
 WHERE it.itemId=ip.itemId 
   AND t.taskId=w.taskId 
   AND w.warehouseId=ip.warehouseId 
   AND t.taskId=1
 GROUP 
    BY it.itemId

此查询给出以下结果：

taskId itemId itemName total_input
1         1     item1      24
1         2     item2      26
1         3     item3      21
1         4     item4      18
1         5     item5      22
1         6     item6       3
1         7     item7      17
1         8     item8      12
1         9     item9      17
1        10     item10     20

这个结果是正确的。10 个输入项。

这两个查询都会生成正确的结果。但是当我尝试将两个查询结合起来以显示两列以及每一列的 SUM 时，问题就来了，结果不好。

组合查询：

SELECT t.taskId
     , it.itemId
     , it.itemName
     , SUM(ip.qty) total_input
     , SUM(u.qty) total_used
  FROM items it
     , task t
     , inputs ip
     , warehouse w
     , used u
 WHERE it.itemId=ip.itemId 
   AND t.taskId=w.taskId 
   AND w.warehouseId=ip.warehouseId 
   AND u.itemId=it.itemId 
   AND u.taskId=t.taskId 
   AND t.taskId=1
 GROUP 
    BY it.itemId

结果：

taskId itemId itemName total_input total_used
1       2       item2       52       90
1       3       item3       21       7
1       4       item4       18       24
1       5       item5       22       30
1       6       item6       3        4
1       8       item8       12       27
1       9       item9       17       20
1       10      item10      20       35

这会产生错误的结果，首先是因为它只显示 8 列而不是 10 列，而且总数也是错误的。我已经搜索并尝试了很多关于如何正确执行此操作的方法，但我只找到了两个表的子查询示例或左连接，这更简单，但是当它带有 5 个涉及所有关系的表时，我不知道该怎么做。

我很感激你能给我的任何帮助。

标签： mysqlsubqueryleft-joinmultiple-tables

解决方案

首先，我将使用 JOIN 转换查询 1 和查询 2，这使其更具可读性。

示例（参见http://www.sql-join.com/）：

select order_date, order_amount
from customers
join orders on customers.customer_id = orders.customer_id

然后为 Query1 和 Query2 创建一个视图：

CREATE VIEW `myView1` AS
    select order_date, order_amount
    from customers
    join orders on customers.customer_id = orders.customer_id

然后你可以做一个新的查询加入两个视图。

祝你好运 ：）

推荐阅读