php - 如何将表连接到此 SQL 代码？

Question

我正在使用来自 @Richard aka cyberkiwi 的精彩代码 运行查询(它返回每个月每个工厂的 value 总和):

表名:data

record_id   id_fk    plant_id_fk    date         value   category_1
1           1          1            2011-03-01   10      A
2           1          1            2011-03-02   10      A
3           1          1            2011-04-10   5       B
4           1          2            2011-04-15   5       C

SQL 代码

    select up.id_fk, up.plant_id_fk, ym2, ifnull(sum(data.value_1),0) totalvalue_1
    from (select distinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2 from data) dates
    cross join (select distinct data.id_fk, data.plant_id_fk from data) up
    left join data on date_format(data.date, '%Y-%m') = dates.ym
        and up.id_fk=data.id_fk
        and up.plant_id_fk=data.plant_id_fk
        and category_1='A'
    group by up.id_fk, up.plant_id_fk, ym2, ym
    order by up.id_fk, up.plant_id_fk, date(concat(ym,'-1'))

现在我需要将它与下表结合起来，以便运行一个 PHP 循环来检索 plant_name 而不是 plant_id。

表名:plants

id_fk    plant_id      plant_name
1        1             oak tree
1        2             cherry tree

有人知道在哪里插入将创建此连接的子句吗？

谢谢!

Answer 1

修改如下图

select
    up.id_fk,
    p.plant_name,
    ym2,
    ifnull(sum(data.value_1),0) totalvalue_1
from (
    select distinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2
    from data) dates
cross join (
    select distinct data.id_fk, data.plant_id_fk
    from data) up
inner join plants p on p.plant_id = up.plant_id_fk
left join data
    on date_format(data.date, '%Y-%m') = dates.ym
    and up.id_fk=data.id_fk
    and up.plant_id_fk=data.plant_id_fk
    and category_1='A'
group by up.id_fk, up.plant_id_fk, ym2, ym, p.plant_name
order by up.id_fk, up.plant_id_fk, date(concat(ym,'-1'))