MySQL可用余额与实际余额不匹配 [英] MySQL Available Balance has not match with Actual Balance

问题描述

我有一个MySQL数据库，用于管理项目的接收和发布。表格之一包括如下。同一项目有不同的价格。

I have a MySQL database that manages receives and issues of items. One of the tables includes as follows. There are different prices available for same item.

+-------------------------+------+-----+------------+
| update_stock_details_id | item | qty | unit_price |
+-------------------------+------+-----+------------+
|                       1 |    4 |   8 |      35.00 |
|                       2 |    4 |  30 |      38.50 |
|                       3 |    4 |  20 |      42.00 |
|                       4 |    4 | -11 |      38.50 |
|                       5 |    4 |  -1 |      38.50 |
|                       6 |    4 |  -1 |      35.00 |
|                       7 |    4 |  -1 |      35.00 |
|                       8 |    4 |  -1 |      35.00 |
|                       9 |    4 |  -1 |      35.00 |
|                      10 |    4 |  -4 |      35.00 |
+-------------------------+------+-----+------------+ 

（-）符号表示表中的问题。然后，在执行问题后，我需要按如下方式获得可用余额。

(-) sign denote the issues in the table. Then I need to get available balances as follows after performing issues.

+------+-----+------------+
| item | qty | unit_price |
+------+-----+------------+
|    4 |  18 |      38.50 |
|    4 |  20 |      42.00 |
+------+-----+------------+

我使用以下查询来做到这一点。

I used the following query to do that.

public function isExistProduct($q)
    {
        if (!empty($q)) {           

            $this->db->select("store_item.*, store_update_stock.*, sum(qty) as qty, unit_price");
            $this->db->from('store_update_stock_details');
            $this->db->join('store_update_stock', 'store_update_stock_details.update_stock_id=store_update_stock.update_stock_id');
            $this->db->join('store_item', 'store_update_stock_details.item=store_item.item_id');            
            $this->db->where("store_update_stock.status=1 and store_item.item_id= $q");
            $this->db->where("store_update_stock_details.qty != 0 ");
            $this->db->group_by('store_update_stock_details.unit_price','store_item.item_id');

            $q1 = $this->db->get();

            if ($q1->num_rows() > 0) {
                return $q1->result_array();
            }
            return 0;
        }
    }

但是该函数返回以下结果：

But the function returns a result as follows :

+------+-----+------------+
| item | qty | unit_price |
+------+-----+------------+
|    4 |   0 |      35.00 |
|    4 |  18 |      38.50 |
|    4 |  20 |      42.00 |
+------+-----+------------+

我不需要获取项目的不可用余额（数量= 0）。我的查询可以更改什么？有人可以帮忙吗？

I do not need to get the balances of unavailability (qty=0) of items. What can be changed in my query ?. Can anyone help ?

推荐答案

您必须使用 having 来过滤结果

文档在这里：

https://codeigniter.com/user_guide/database/query_builder.html#look-for-like-data

所以在您的情况下：

$this->db->having('qty <> 0 '); 

这篇关于MySQL可用余额与实际余额不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！