带有多个子记录的正确总和（PostgreSQL） [英] Correct sum with multiple subrecords (postgresql)

问题描述

这可能已经被问过几次了，但是我没有从父母和孩子那里获得正确的礼物。

This have maybe been asked several times before, but I do not how to achieve a correct som from both parent and child.

这里是桌子：

CREATE TABLE co
    (coid int4, coname text);    
INSERT INTO co
    (coid, coname)
VALUES
    (1, 'Volvo'),
    (2, 'Ford'),
    (3, 'Jeep'),
    (4, 'Toyota')

;

CREATE TABLE inv
    (invid int4, invco int4, invsum numeric(10,2));
INSERT INTO inv
    (invid, invco, invsum)
VALUES
    (1,1,100),
    (2,1,100),
    (3,2,100),
    (4,3,100),
    (5,4,100)
;

CREATE TABLE po
    (poid int4, poinv int4, posum int4);
INSERT INTO po
    (poid, poinv, posum)
VALUES
    (1,1,50),
    (2,1,50),
    (3,3,100),
    (4,4,100)
;

我从这个简单的查询开始

I started with this simple query

SELECT    coname, sum(invsum)
FROM      inv
LEFT JOIN co ON coid=invco
GROUP BY  1
ORDER BY  1

哪个给出了正确的结果：

Which gave a correct result:

coname     sum
Ford       100
Jeep       100
Toyota     100 
Volvo      200

然后我添加了po记录，总和变得不正确：

Then I added the po record and the sums became incorrect:

SELECT    coname, sum(posum) as po, sum(invsum)
FROM      inv
LEFT JOIN co ON coid=invco
LEFT JOIN po ON poinv=invid
GROUP BY  1
ORDER BY  1

乘以沃尔沃的总和：

coname    po     sum
Ford      100    100
Jeep      100    100
Toyota    (null) 100 (no records for po = correct)
Volvo     100    300 (wrong sum for inv)

我如何构造一个查询，给出带有多个po子记录的正确结果？ （窗口函数？）

How do I construct a query that gives correct result with multiple subrecords of po? (Window function?)

Sqlfiddle： http ：//sqlfiddle.com/#！15 / 0d90c / 12

Sqlfiddle: http://sqlfiddle.com/#!15/0d90c/12

推荐答案

请先进行汇总 联接。这在您的情况下有点复杂，因为 co <​​/ code>和 po 之间的关系似乎需要 inv ：

Do the aggregation before the joins. This is a little complicated in your case, because the relationship between co and po seems to require inv:

SELECT co.coname, p.posum, i.invsum
FROM co LEFT JOIN
     (SELECT i.invco, sum(i.invsum) as invsum
      FROM inv i
      GROUP BY i.invco
     ) i
     ON co.coid = i.invco LEFT JOIN
     (SELECT i.invco, sum(po.posum) as posum
      FROM po JOIN
           inv i
           ON po.poinv = i.invid
      GROUP BY i.invco
     ) p
     ON co.coid = p.invco
ORDER BY 1;

注意：我想逻辑是将所有内容保留在 co <​​/ code>表，即使其他表中没有匹配项也是如此。 LEFT JOIN 应该从此表开始，该表包含要保留的所有行。

Note: I presume the logic is to keep everything in the co table, even if there are no matches in the other tables. The LEFT JOIN should start with this table, the one with all the rows you want to keep.

这篇关于带有多个子记录的正确总和（PostgreSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！