MySQL查询-将4个表连接在一起,其中3个表使用每个表的一列分组 [英] MySQL query - join 4 tables together, with 3 tables using group by one column from each
本文介绍了MySQL查询-将4个表连接在一起,其中3个表使用每个表的一列分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下是我正在使用的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 item
s table with all available date
s in the three other tables, and then the aggregations from the three tables with left join
s:
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屋!
查看全文