有关sum表总计sql代码符号的问题 [英] Issue on sum table total sql codeigniter

问题描述

我有一个问题，当我想通过id做列的总和。

表产品

  ID代码名称价格数量
 522 123产品1 12 4 
  

表purchase_items

  id product_id quantity 
 1 522 5 
 2 522 1 
  

表sale_items

 code> id product_id quantity 
 1 522 1 
 2 522 1 
  

function codeigniter：

  $ products = $ this-> alerts-> select（'products.id as productid，products .code as code，products.name，products.price，sum（sale_items.quantity）'）
  - > from（'products'）; 
 $ this-> alerts-> join（'sale_items'，'products.id = sale_items.product_id'）; 
 $ this-> alerts-> group_by（products.id）; 
 
 $ products = $ this-> alerts-> select（'products.id as productid，products.code as code，products.name，products.price，sum（purchase_items.quantity）' ）
  - > from（'products'）; 
 $ this-> alerts-> join（'purchase_items'，'products.id = purchase_items.product_id'）; 
 $ this-> alerts-> group_by（products.id）; 
 
 
 $ this-> alerts-> unset_column（'productid'）; 
 
 foreach（$ this-> result_array（）as $ product）
 {
 $ products [] = $ product ['record']; 
} 
 
 return $ products; 
} 
  

问题是

  purchase_items结果为12 
 sale_items结果为4 
  

为什么要取结果x 2？

任何帮助是值得赞赏的。

解决方案

因为您正通过您的商品主档（产品表）加入购买活动到销售活动。因此，包含在总金额中的购买行数取决于销售中有多少行;反之亦然销售。

您需要将这些查询作为在视图中组合的单独的SQL语句分隔，或者您可以使用SQL UNION ALL 组合两个语句：一个用于购买，一个用于销售。

两个语句：

  $ this-> alerts 
  - > select（'products.id as productid，products.code as code，products.name，products.price，sum（purchase_items.quantity）'）
  - > from（'products'）; 
 $ this-> alerts-> join（'purchase_items'，'products.id = purchase_items.product_id'）; 
 $ this-> alerts-> group_by（products.id）; 
 echo总购买; 
 echo $ this-> alerts-> generate（）; 
 
 
 $ this-> alerts 
  - > select（'products.id as productid，products.code as code，products.name，products.price，sum sale_items.quantity）'）
  - > from（'products'）; 
 $ this-> alerts-> join（'sale_items'，'products.id = sale_items.product_id'）; 
 $ this-> alerts-> group_by（products.id）; 
 echoTotal Sales; 
 echo $ this-> alerts-> generate（）; 
  

或在SQL中：

  $ results = $ this-> db-> query（'
 SELECT products.id as productid，products.code as code，products.name，products.price，''购买作为交易，总和（purchase_items.quantity）
从产品
 JOIN purchase_items ON products.id = purchase_items.product_id 
 GROUP BY products.id 
 UNION ALL 
 SELECT products.id as productid，products.code as code，products.name，products.price，''Sale''as transaction sum（sales_items.quantity）
 FROM products 
 JOIN sales_items ON products。 id = sales_items.product_id 
 GROUP BY products.id'） - > result_array（）; 
 
 // Echo $ results .... 
  

：
我不完全确定您的警报模型中有什么，但在修改的查询中，您将一个SQL语句分配给 $ products 但不执行语句？

还要确保您的Alerts模型扩展在更标准的Codeigniter语法中，我会做这样的：

  //在控制器中，假设加载了警报模型，并使用方法链。 
 
 //获取所有销售。顺便说一句，你通常必须对所有列*不*聚合进行分组。你可能
 //通过对价格进行分组得到意想不到的结果，除非价格从不改变。 
 $ this-> alerts-> select（'products.id as productid，products.code，products.name，products.price，sum（sale_items.quantity）as quantity'）
  - > ; from（'products'）
  - > join（'sale_items'，'products.id = sale_items.product_id'）
  - > group_by（'products.id，products.code，products。 name，products.price'）; 
 $ sales = $ this-> alerts-> get（） - > result_array（）; 
 
 //取得所有购买
 $ this-> alerts-> select（'products.id as productid，products.code，products.name，products.price，sum（purchase_items .quantity）as quantity'）
  - > from（'products'）
  - > join（'purchase_items'，'products.id = purchase_items.product_id'）
  - > group_by（products.id，products.code，products.name，products.price）; 
 $ purchases = $ this-> alerts-> get（） - > result_array（）; 
 
 //放在一起
 $ products = array_merge（$ sales，$ purchases）; 
 
 //现在对你的结果$ products做一些事情。发送到查看，回显等。
  

I have a little problem when I want to make the total sum of the column by id.

Table products

id   code    name        price  quantity
522  123     Product 1     12      4

Table purchase_items

id  product_id  quantity
1      522            5
2      522            1

Table sale_items

id  product_id  quantity
1      522            1
2      522            1

function codeigniter:

$products = $this->alerts->select('products.id as productid, products.code as code, products.name, products.price, sum(sale_items.quantity)')
      ->from('products');
      $this->alerts->join('sale_items', 'products.id = sale_items.product_id');           
      $this->alerts->group_by("products.id");

      $products = $this->alerts->select('products.id as productid, products.code as code, products.name, products.price, sum(purchase_items.quantity)')
      ->from('products');
      $this->alerts->join('purchase_items', 'products.id = purchase_items.product_id');           
      $this->alerts->group_by("products.id");


        $this->alerts->unset_column('productid');

        foreach ($this->result_array() as $product)
        {
            $products[] = $product['record'];
        }

        return $products;
    }

The issue is that the result for

purchase_items the result is 12
sale_items      the result is 4

Why is taking the result x 2 ?

Any help is appreciated.

解决方案

Because you are joining purchasing activity to sales activity via your item master (products table). As a result, the number of rows for purchasing that are included in the sum depend on how many rows in sales you have; and vice versa for sales.

You need to separate those queries either as separate SQL statements that you combine in the view, or you can use a SQL UNION ALL to combine two statements: one for purchases and one for sales.

Two statements:

$this->alerts
            ->select('products.id as productid, products.code as code, products.name, products.price, sum(purchase_items.quantity)')
            ->from('products');
            $this->alerts->join('purchase_items', 'products.id = purchase_items.product_id');
                $this->alerts->group_by("products.id");
            echo "Total Purchases";
            echo $this->alerts->generate();


 $this->alerts
            ->select('products.id as productid, products.code as code, products.name, products.price, sum(sale_items.quantity)')
            ->from('products');
            $this->alerts->join('sale_items', 'products.id = sale_items.product_id');           
                $this->alerts->group_by("products.id");
            echo "Total Sales";
            echo $this->alerts->generate();

Or, in SQL:

$results = $this->db->query('
    SELECT products.id as productid, products.code as code, products.name, products.price, ''Purchase'' as transaction, sum(purchase_items.quantity)
    FROM products
    JOIN purchase_items ON products.id = purchase_items.product_id
    GROUP BY products.id
    UNION ALL
    SELECT products.id as productid, products.code as code, products.name, products.price, ''Sale'' as transaction sum(sales_items.quantity)
    FROM products
    JOIN sales_items ON products.id = sales_items.product_id
    GROUP BY products.id')->result_array();

// Echo $results....

Edit: I'm not entirely sure what you have in your Alerts model, but in your revised query you are assigning a SQL statement twice to the variable $products but not executing the statements?

Also make sure your Alerts model extends CI_Model.

In more standard Codeigniter syntax, I would do something like this:

    // In Controller, assuming the Alerts model is loaded, and using method chaining.

    // Get all sales. By the way, you normally have to group on all columns *not* being aggregated. You might
    // get unexpected results by grouping on price, unless the price never changes.
    $this->alerts->select('products.id as productid, products.code, products.name, products.price, sum(sale_items.quantity) as quantity')
          ->from('products')
          ->join('sale_items', 'products.id = sale_items.product_id')
          ->group_by('products.id, products.code, products.name, products.price');
    $sales = $this->alerts->get()->result_array();

    // Get all purchases
    $this->alerts->select('products.id as productid, products.code, products.name, products.price, sum(purchase_items.quantity) as quantity')
          ->from('products')
          ->join('purchase_items', 'products.id = purchase_items.product_id')
          ->group_by(products.id, products.code, products.name, products.price);
    $purchases = $this->alerts->get()->result_array();

    // Put it all together
    $products = array_merge($sales, $purchases);

    // And now do something with your combined results $products. Send to view, echo etc.

这篇关于有关sum表总计sql代码符号的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！