MySQL查询-将4个表连接在一起，其中3个表使用每个表的一列分组 [英] MySQL query - join 4 tables together, with 3 tables using group by one column from each

问题描述

以下是我正在使用的4个表的示例.

Here are examples of the 4 tables I'm working with.

Items
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | def  |
|  3 | ghi  |
+----+------+


Buy Table
+----+-------------+-----+---------+
| id | date        | qty | item_id |
+----+-------------+-----+---------+
|  1 | 2020-05-01  | 10  |    1    |
|  2 | 2020-05-02  | 20  |    2    |
|  3 | 2020-05-03  | 5   |    3    |
+----+-----------+-------+---------+


Rent Table
+----+-------------+-----+---------+
| id | date        | qty | item_id |
+----+-------------+-----+---------+
|  1 | 2020-05-02  | 5   |    2    |
|  2 | 2020-05-03  | 10  |    2    |
|  3 | 2020-05-04  | 15  |    3    |
+----+-----------+-------+---------+


Sell Table
+----+-------------+-----+---------+
| id | date        | qty | item_id |
+----+-------------+-----+---------+
|  1 | 2020-05-03  | 10  |    1    |
|  2 | 2020-05-05  | 20  |    3    |
|  3 | 2020-05-06  | 5   |    3    |
+----+-----------+-------+---------+

我正试图用php foreach来获得类似这样的输出...

And I'm trying to get outputs with php foreach something like this ...

In case item_id "1"
+-------------+--------------+---------------+---------------+------+
| date        | BUY SUM(qty) | RENT SUM(qty) | SELL SUM(qty) | Name |
+-------------+--------------+---------------+---------------+------+
| 2020-05-01  |       10     |       0       |       0       | abc  |
| 2020-05-02  |       0      |       0       |       0       | abc  |
| 2020-05-03  |       0      |       0       |       10      | abc  |
| 2020-05-04  |       0      |       0       |       0       | abc  |
| 2020-05-05  |       0      |       0       |       0       | abc  |
| 2020-05-06  |       0      |       0       |       0       | abc  |
+-------------+--------------+---------------+---------------+------+

这是我要查询的一张桌子...

This is the query I've come for one table...

SELECT date AS date, 
       SUM(qty) AS qty 
FROM buy_table 
WHERE item_id='1' 
AND MONTH(date)=MONTH(CURDATE()) 
GROUP BY DATE(date)

推荐答案

您可以cross join将item s表与其他三个表中的所有可用date一起使用，然后将三个表的聚合与left join s:

You can cross join the items table with all available dates in the three other tables, and then the aggregations from the three tables with left joins:

select d.date, b.qty_buy, r.qty_rent, s.qty_sell, i.name
from items i
cross join (
    select date from buy 
    union all select date from rent 
    union all select date from sell
) d
left join (select date, item_id, sum(qty) qty_buy  from buy  group by date, item_id) b
    on b.date = d.date and b.item_id = i.id
left join (select date, item_id, sum(qty) qty_rent from rent group by date, item_id) r
    on r.date = d.date and r.item_id = i.id
left join (select date, item_id, sum(qty) qty_sell from sell group by date, item_id) s
    on s.date = d.date and s.item_id = i.id
where i.id = 1 and d.date >= date_format(curent_date, '%Y-%m-01')
order by d.date

这篇关于MySQL查询-将4个表连接在一起，其中3个表使用每个表的一列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！