在连接到表时(左)保留数组的所有元素 [英] Preserve all elements of an array while (left) joining to a table

问题描述

我正尝试从费用表中逐月提取所有12个月的费用. 如果一个月内没有结果，我仍然希望本月收到价值为0(零)的结果.

I'm trying to pull from my expenses table all 12 months expenses, group by month. If there are no results in one month, I still wish to receive this month worth 0 (zero) value result.

我的数据库是PostgreSQL 9.1.

My database is PostgreSQL 9.1.

我写了这个查询，但是我没有成功执行它或不知道如何纠正它.

I wrote this query, but I don't succeed to execute it or understand how to correct it.

SELECT fta.liability_id, fta.amount
     , date_part('month', fta.act_date) AS act_month
FROM  (
   select i
   from generate_series(1,array_upper(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12],1)) i) as ym
LEFT OUTER JOIN financial_t_account fta on ym.i = fta.act_month;

推荐答案

错误是您试图在JOIN条件下引用输出列的名称(act_month)，在该条件下该输出列的名称不可见-无法计算然而！试试:

The fault is that you try to refer to an output column's name (act_month) in the JOIN condition, where it is not visible - it is not calculated yet! Try:

SELECT fta.liability_id
      ,fta.amount
      ,date_part('month', fta.act_date) AS act_month
FROM  (
   SELECT i
   FROM   generate_series(1,array_upper(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12],1)) i
   ) ym
LEFT JOIN financial_t_account fta
          ON ym.i = date_part('month', fta.act_date)::int;

您可以大致简化为:

SELECT fta.liability_id
      ,fta.amount
      ,date_part('month', fta.act_date) AS act_month
FROM   generate_series(1,12) ym(i)
LEFT   JOIN financial_t_account fta
            ON ym.i = date_part('month', fta.act_date)::int;

或者，如果您希望从数组中获得任意月份，请使用 unnest() 而不是 generate_series() :

Or, if you want arbitrary months from an array, use unnest() instead of generate_series():

... 
FROM   unnest(ARRAY[1,2,4,57,8,11,12]) ym(i)
...

使用 CTE (或子查询)来避免重复该表达式(并对其进行两次评估):

Use a CTE (or a subquery) to avoid repeating the expression (and evaluating it twice):

WITH x AS (
   SELECT *, date_part('month', act_date)::int As act_month
   FROM financial_t_account
)
SELECT x.liability_id
      ,x.amount
      ,x.act_month
FROM   generate_series(1,12) ym(i)
LEFT   JOIN x ON ym.i = x.act_month;

这篇关于在连接到表时(左)保留数组的所有元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！