sql - 如何从子查询中引用函数结果列？

Question

我正在尝试提出一个查询，该查询计算按年份分组的行，我必须从 TIMESTAMP 和 airline_id 获得该查询。到目前为止，我已经想出了这个:

SELECT airline_id, extract(year from departure)::integer as year,
(SELECT count(*) FROM tickets AS t2 LEFT JOIN flights AS f2 ON t2.flight_id = f2.flight_id WHERE extract(year from f2.departure) = year AND f2.airline_id = f.airline_id)
FROM tickets AS t
LEFT JOIN flights AS f ON t.flight_id = f.flight_id
GROUP BY airline_id, year
ORDER BY airline_id ASC, year ASC;

它似乎可以正常工作，除了它会抛出这个错误:

ERROR: column "year" does not exist

我尝试将其引用为:

SELECT airline_id, extract(year from departure)::integer as year,
(SELECT count(*) FROM tickets AS t2 LEFT JOIN flights AS f2 ON t2.flight_id = f2.flight_id WHERE extract(year from f2.departure) = t.year AND f2.airline_id = f.airline_id)
FROM tickets AS t
LEFT JOIN flights AS f ON t.flight_id = f.flight_id
GROUP BY airline_id, year
ORDER BY airline_id ASC, year ASC;

但它做同样的事情。您也不能再次调用该函数，因为

ERROR: subquery uses ungrouped column "f.departure" from outer query

那么我应该如何从子查询中引用“year”列呢？

Answer 1

让我们从这个查询开始:

SELECT f.airline_id,  -- I speculate that the airline comes from the flights table
       extract(year from f.departure) as year,
       COUNT(*)
FROM tickets t LEFT JOIN
     flights f 
     ON t.flight_id = f.flight_id
GROUP BY f.airline_id, year
ORDER BY airline_id ASC, year ASC;

它做你想做的事吗？您的子查询似乎没有做任何有用的事情。