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 |
+----+-----------+-------+---------+
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)
解决方案
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
1010
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
