在codeigniter上从SQL汇总多于一个表

need a small help to figure out this situation. I see that codeigniter is not supporting UNION and i want to figure out this method to get the total sum for 2 tables, with same id of product.

需要一点帮助来弄清楚这种情况。我看到codeigniter不支持UNION,我想找出这个方法来获得2个表的总和,具有相同的产品ID。

The issue is that on sum 2 tables, the values are duplicating or multiplicative.

问题是在和2表上,值是重复的或乘法的。

Link to SQL Fiddle

链接到SQL Fiddle

Structure :

create table products
(id int, name varchar(9));
insert into products 
(id, name)
values
(1,'Product 1'),
(2,'Product 2'),
(3,'Product 3');


create table p_items
(puid int, prid int, quantity int);
insert into p_items
(puid, prid, quantity)
values
(11,1,100),
(11,2,100),
(11,2,100),
(14,2,100),
(14,3,100),
(15,3,100);

create table s_items
(puid int, prid int, quantity int);
insert into s_items
(puid, prid, quantity)
values
(11,1,1),
(11,2,1),
(11,2,1),
(13,2,1),
(15,3,1),
(13,3,1);

Execute in normal SQL:

在普通的SQL中执行:

select a.puid, b.name, a.prid, sum(a.quantity) as P_ITEMS, sum(c.quantity) as S_ITEMS
from p_items a
join products b
on b.id = a.prid
join s_items c
on c.prid = a.prid
group by a.prid;

Codeigniter Function:

$this->alerts
            ->select('products.id as productid, products.name, sumt(p_items.quantity), sum(s_items.quantity)', FALSE)
            ->from('products')
            ->join('p_items', 'products.id = p_items.prid', 'left')
            ->join('s_items', 'products.id = s_items.prid')
            ->group_by("products.id");
            echo $this->alerts->generate();

Any help is very appreciated.

非常感谢任何帮助。

2 个解决方案

#1

Your problem is your producing a cartesian product and thus getting your duplicated sums. Look at Product ID 3 for example. You're associating that with p_items prid = 3. By itself, that would return you 200. However, once you then join on s_items prid = 3, now for each row in s_items, it has to match with each row in p_items. Meaning:

你的问题是你生产笛卡尔积,从而获得重复的总和。例如,查看产品ID 3。你将它与p_items prid = 3相关联。它本身会返回200.但是,一旦你加入s_items prid = 3,现在对于s_items中的每一行,它必须与p_items中的每一行匹配。含义:

14 3 100 15 3 1
14 3 100 15 3 1
15 3 100 15 3 1
15 3 100 15 3 1
---------------
     400      4

Besides the product table, which table is your master table? If you use p_items, you want get row 13 from s_items. Likewise, if you use s_items, you won't get rows 14 from p_items.

除了产品表,哪个表是你的主表?如果使用p_items,则需要从s_items获取第13行。同样,如果使用s_items,则不会从p_items获取第14行。

You can accomplish this using a subquery:

您可以使用子查询来完成此操作:

select b.id, 
  b.name, 
  P_ITEMS, 
  S_ITEMS
from products b
  left join 
    (SELECT prid, SUM(quantity) as P_Items
     FROM p_items
     GROUP BY prid)
     a on b.id = a.prid
  left join 
    (SELECT prid, SUM(quantity) as S_Items
     FROM s_items
     GROUP BY prid)
     c on c.prid = a.prid
group by b.id, b.name

And the updated Fiddel: http://sqlfiddle.com/#!2/62b45/12

更新的Fiddel:http://sqlfiddle.com/#!2/62b45/12

Good luck.

#2

If the best way to get the answer you want is with a union query, and codeigniter does not let you write one, don't use codeigniter to write your query. Put it in a stored procedure and call the stored procedure with codeigniter.

如果获得所需答案的最佳方法是使用联合查询,并且codeigniter不允许您编写一个,请不要使用codeigniter来编写查询。将其放入存储过程并使用codeigniter调用存储过程。

#1

14 3 100 15 3 1
14 3 100 15 3 1
15 3 100 15 3 1
15 3 100 15 3 1
---------------
     400      4