在视图上构建Postgresql视图

Question

我的一个OpenERP客户遇到问题。我需要为他们建立一个自定义对象，因为他们有一个问题，有时他们会在产品售出后收到退货。所以我需要将其标记为负数，并从之前的总销售额中减去。

SELECT sum(rsm1.product_qty_out) as "out"
     , sum(rsm2.product_qty_in) as "in"
     , rsm2.location_id
     , rsm2.product_id
     , rsm2.month
from report_stock_move as rsm1, report_stock_move as rsm2
where rsm2.location_id in (
    select id
    from stock_location
    where "name" = 'Consumers'
    )
and rsm1.location_id not in (
    select id
    from stock_location
    where "name" = 'Consumers'
    )
and rsm1.location_dest_id = rsm2.location_id
and rsm2.location_dest_id = rsm1.location_id
and rsm1.state = 'done' -- both are in done state
and rsm2.state = rsm1.state
group by rsm2.location_id, rsm2.month, rsm2.product_id
;

有人有什么想法吗？此外，根据分组使用的表，我会得到不同的结果。为什么？

编辑对不起，在我急于解决这个问题的过程中，我似乎已经把我的观点说清楚了。来自stock_location表的location_id为Consumers的report_stock_moves需要为负数，并将其汇总为具有另一个位置的location_id的report_stlock_moves，但Consumer是location_dest_id。

示例：

location_id = Some Place (actually ID of stock_location of course)
product_qty = 8
location_dest_id Consumers
date = 2012-07-02
location_id = Consumer
product_qty = 4
location_dest_id Some Place
date = 2012-07-04

以下是两个记录示例。它们有不同的日期，所以如果我想将它们合并或分组，我可能想取最大日期，或者退货日期（从消费者到某个地方），这样当它们组合在一起时，我会得到4，而不是说8，如果我不将它们组合在一起，只看消费者的情况。

Answer 1

以防以后有人为此而烦恼。我将把我的答案粘贴在这里。若有人能帮我解释一下为什么这里的工会似乎有效，我洗耳恭听。我可以做简单的SQL，但我不太了解工会是如何工作的，尤其是在这种情况下。

select max(total.id) as id,
                     sum(total.product_qty) as product_qty,
                     total.location_id as location_id,
                     total.date as "date",
                     total.year as year,
                     total.month as month,
                     total.day as day,
                     total.product_id as product_id
                     from (
                       -- From Consumer back to location
                        select -max(id) as id,
                        -sum(product_qty) as product_qty, 
                        location_dest_id as location_id, 
                        "date", year, month, day, product_id
                        from report_stock_move
                        where location_id in (
                            select id
                            from stock_location
                            where "name" = 'Consumers'
                        )
                        and "state" = 'done'
                        group by location_dest_id, "date", year, month, day, product_id
                        union all
                        -- From Location to Consumer
                        select max(id) as id,
                        sum(product_qty) as product_qty, 
                        location_id, "date", year, month, day, product_id
                        from report_stock_move
                        where location_id not in (
                            select id
                            from stock_location
                            where "name" = 'Consumers'
                        )
                        and "state" = 'done'
                        and location_dest_id in (
                            select id
                            from stock_location
                            where "name" = 'Consumers'
                        )
                        group by location_id, "date", year, month, day, product_id
                     ) as total
                     group by location_id, "date", year, month, day, product_id