如何将表联接到此SQL代码? [英] How to join a table to this SQL code?

问题描述

我正在使用以下很棒的代码，从 @Richard aka cyberkiwi 运行查询(它返回每个工厂每个月的value之和):

I am using this awesome code from @Richard aka cyberkiwi to run a query (it returns the sum of value for each month, for each plant):

表名:数据

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.

Now I need to join this with the following table in order to run a PHP loop that will retrieve the plant_name instead of the plant_id.

表名:植物

id_fk    plant_id      plant_name
1        1             oak tree
1        2             cherry tree

任何人都知道在哪里插入将创建此联接的子句吗?

Anyone know where to insert the clause that would create this join?

谢谢！

推荐答案

修改如下所示

选择
    up.id_fk，
    植物名称，
    ym2，
    ifnull(sum(data.value_1)，0)totalvalue_1
来自(
   选择不同的date_format(date，'％Y-％m')ym，date_format(date，'％M％Y')ym2
   来自数据)日期
交叉连接(
   选择不同的data.id_fk，data.plant_id_fk
   从数据开始)
内部加入p.plant_id = up.plant_id_fk上的植物
左联接数据
   在date_format(data.date，'％Y-％m')= dates.ym
   和up.id_fk = data.id_fk
   以及up.plant_id_fk = data.plant_id_fk
   和category_1 ='A'
按up.id_fk，up.plant_id_fk，ym2，ym， p.plant_name
分组 按up.id_fk，up.plant_id_fk，date(concat(ym，'-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'))

这篇关于如何将表联接到此SQL代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！