mysql query-将4个表连接在一起，其中3个表使用groupby，每个表中有一列

wvmv3b1j 于 2021-08-09 发布在 Java

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

下面是我正在使用的4个表的示例。

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获取输出，比如这样的东西。。。

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  |
+-------------+--------------+---------------+---------------+------+

这是我来查询的一个表。。。

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

sql mysql Join group-by sum

来源：https://stackoverflow.com/questions/61875189/mysql-query-join-4-tables-together-with-3-tables-using-group-by-one-column-fr

1条答案

按热度按时间

rbl8hiat1#

你可以 cross join 这个 item s桌上有所有可用的 date 其他三个表中的，然后三个表中的聚合 left join 学生：

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

赞(0）回复(0）举报 2021-08-09

我来回答

mysql query-将4个表连接在一起，其中3个表使用groupby，每个表中有一列

1条答案

相关问题

热门标签

最新问答