PHP Codeigniter MySQL 查询 - 将 4 个表连接在一起，其中 3 个表使用每个表中的一列分组

Question

>Items Table

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

购买表

+------+-------------+-------+---------+
| b_id | b_date      | b_qty | b_itmid |
+------+-------------+-------+---------+
|  1   | 2020-05-01  |  10   |    1    |
|  2   | 2020-05-01  |  20   |    1    |
|  3   | 2020-05-02  |  5    |    2    |
|  3   | 2020-05-03  |  10   |    3    |
+------+-------------+-------+---------+

租金表

+------+-------------+-------+---------+
| r_id | r_date      | r_qty | r_itmid |
+------+-------------+-------+---------+
|  1   | 2020-05-03  |   5   |    2    |
|  2   | 2020-05-03  |   10  |    2    |
|  3   | 2020-05-04  |   15  |    3    |
+------+-----------+---------+---------+

卖出表

+------+-------------+-------+---------+
| s_id | s_date      | s_qty | s_itmid |
+------+-------------+-------+---------+
|  1   | 2020-05-03  |  10   |    1    |
|  2   | 2020-05-05  |  20   |    3    |
|  3   | 2020-05-06  |  5    |    3    |
+------+-----------+---------+---------+

我正在尝试通过这样的东西php foreach输出......

$trans_date
$buy_qty
$rent_qty
$sell_qty
$item

如果项目ID1

+-------------+--------------+---------------+---------------+------+
| trans_date  |   buy_qty    |    rent_qty   |   sell_qty    | item |
+-------------+--------------+---------------+---------------+------+
| 2020-05-01  |       30     |       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  |
+-------------+--------------+---------------+---------------+------+

这是我为一个表提供的查询(b_date列具有时间戳值(...

$query  = $this->db->query("
SELECT FROM_UNIXTIME(b_date,'%d %M') AS date_b
, SUM(b_qty) AS qty_b 
FROM buytable 
WHERE b_itmid = 1
AND MONTH(FROM_UNIXTIME(b_date)) = MONTH(CURDATE()) 
GROUP 
BY DATE(FROM_UNIXTIME(b_date))
");
if ($query->num_rows() > 0) {
foreach ($query->result() as $data) {
$result[] = $data;
}
return $result;
}

Answer 1

最后我找到了正确的答案 https://stackoverflow.com/a/61875506/7554875

select d.date, b.qty_buy, r.qty_rent, s.qty_sell, i.name
from items i
cross join (
select b_date as date from buy 
union all select r_date as date from rent 
union all select s_date as date from sell
) d
left join (select b_date as date, b_itmid, sum(b_qty) qty_buy  
from buy  group by date, b_itmid) b
on b.date = d.date and b.b_itmid = i.id
left join (select r_date as date, r_itmid, sum(r_qty) qty_rent 
from rent group by date, r_itmid) r
on r.date = d.date and r.r_itmid = i.id
left join (select s_date as date, s_itmid, sum(s_qty) qty_sell 
from sell group by date, s_itmid) s
on s.date = d.date and s.b_itmid = i.id
where i.id = 1 and d.date >= date_format(curent_date, '%Y-%m-01')
order by d.date