dragon19720808
2014-09-09 00:42
浏览 117
已采纳

查询从一个表中获取条件的总和,并将其添加到另一个表中的相同条件

What I have is two tables;

heli_acft:

  • acft_id
  • acft_reg
  • acft_hours

heli_flt:

  • flt_id
  • flt_acft
  • flt_colhr

acft_id correlates to flt_acft.

I want to get the total sum of all flt_colhr of each flt_acft in the table, and then add that figure to the value of acft_hours per acft_id.

The result will then be displayed as a <?php echo data[???] ?> in a table.

Any ideas on how I could do this?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douna4762 2014-09-09 01:14
    已采纳

    Assuming that acft_id is a unique idenfifier in flt_acft table...

    Here is one approach to returning the specified resultset, adding the total of "hours" (from heli_flt to the "hours" from heli_acft:

     SELECT a.acft_id
          , a.acft_hours
          , IFNULL(f.flt_hours,0)
          , a.acft_hours + IFNULL(f.flt_hours,0) AS total_hours
       FROM heli_acft a
       LEFT
       JOIN ( SELECT SUM(h.flt_colhr) AS flt_hours
                   , h.flt_acft
                FROM heli_flt h
               GROUP BY h.flt_acft
            ) f
         ON f.flt_acft = a.acft_id
    

    The inline view calculates f calculates the sum of the hours from heli_flt for each flt_acft. A JOIN to heli_acft to get the acft_hours, and we simply add the values of the two columns. We use an "outer" join so we can return rows from heli_acft that don't have any related rows in heli_flt, and use an IFNULL() function to replace a NULL value with a zero.

    点赞 打赏 评论
  • dream890110 2014-09-09 00:46

    Join the tables and calculate the sum. I use a LEFT JOIN to get a zero result for aircraft that have no flights.

    SELECT acft_id, acft_hours + IFNULL(SUM(flt_colhr), 0) AS total_colhr
    FROM heli_acft 
    LEFT JOIN heli_flt ON acft_id = flt_acft
    GROUP BY acft_id
    
    点赞 打赏 评论

相关推荐 更多相似问题