mysql - Codeigniter框架中多列合计值为0时，项目id为null

Question

我编写了一个查询来获取多列总和值。但是当多列总和为 0 时，我的 item_id 返回 null。因此，我的列表项无法显示千项。

我的查询:

   SELECT
    phppos_sales_items.item_id, IFNULL(SUM(trans_inventory),0) AS Total
FROM
    phppos_sales_items inner join phppos_inventory on phppos_sales_items.item_id = phppos_inventory.trans_items
WHERE
    phppos_inventory.trans_comment LIKE 'RECV %' and phppos_inventory.trans_items in ( '1486')

我的结果:

Answer 1

左连接

我想要 phpos_sales_items 中的所有记录以及 phppos_inventory 中匹配的记录

如果保留 where 子句，则会消除左连接将保留为“null”的记录

SELECT phppos_sales_items.item_id
     , SUM(coalesce(trans_inventory,0)) AS Total
FROM  phppos_sales_items 
LEFT join phppos_inventory 
  on phppos_sales_items.item_id = phppos_inventory.trans_items
 AND phppos_inventory.trans_comment LIKE 'RECV %' 
WHERE phppos_sales_items .trans_items in ( '1486')