在PostgreSQL CTE中进行多重选择-Java 学习之路

我试图在PostgreSQL中将临时表转换为CTE http://www.postgresql.org/docs/9.1/static/queries-with.html . 在存储过程中，我创建了一个临时表，并使用两个不同的选择查询将其插入临时表两次 . 但转换到CTE时，我该如何实现？它不支持多次选择

CREATE TEMPORARY TABLE breakup_amount
  (
  estimate_id integer,
  breakup_total numeric
  )
  ON COMMIT DROP;

Insert Into breakup_amount
Select SP.estimate_id,
       sum(SP.from_bank+SP.from_customer) as breakup_total
                    FROM sales_payment_breakups SP 
where 
SP.breakup_date <= due_date and SP.milestone_id is null
group by SP.estimate_id;


Insert Into breakup_amount
Select SP.estimate_id,
       sum(SP.from_bank+SP.from_customer) as breakup_total
                    FROM sales_payment_breakups SP 
where 
SP.breakup_date >= due_date and SP.project_id is null
group by SP.estimate_id;

我可以写第一个插入

with breakup_amount as (
Select SP.estimate_id,
           sum(SP.from_bank+SP.from_customer) as breakup_total
                        FROM sales_payment_breakups SP 
    where 
    SP.breakup_date <= due_date and SP.milestone_id is null
    group by SP.estimate_id
)

但那我该如何进行第二次插入呢？

1 回答

如果你需要做两个 SELECT 来将类似的数据（即相同的列/数据类型）组合到一个结果集中，你可以使用 UNION 而不是CTE：

SELECT SP.estimate_id, sum(SP.from_bank+SP.from_customer) AS breakup_total
  FROM sales_payment_breakups SP 
  WHERE SP.breakup_date <= due_date AND SP.milestone_id IS NULL
  GROUP BY SP.estimate_id
UNION
SELECT SP.estimate_id, sum(SP.from_bank+SP.from_customer) AS breakup_total
  FROM sales_payment_breakups SP 
  WHERE SP.breakup_date >= due_date AND SP.project_id IS NULL
  GROUP BY SP.estimate_id;

回复于 2024-05-06T02:30:17+08:00

在PostgreSQL CTE中进行多重选择

1 回答

相关问题