PostgreSQL子查询作为可用变量

Question

我想使用一个子查询，将其输出到另一个计算中的列中。我该怎么做？

SELECT c_id, c_title, c_enrolcap,
(SELECT COUNT(e_id) AS enrol FROM enrollments WHERE e_c_id = c_id) AS enrolled,
c_enrolcap - enrolled AS avail,
FROM classes AS c

基本上，enrolled的输出结果需要作为一列数据后续进行计算，并且也需要作为它自己的一列数据。

- jfreak53

---

以上查询是否预期返回单行或多行？ - Eric Hauenstein

多行，确切地说是许多行。 - jfreak53

Answer 1

我通常使用公共表达式来完成这个操作。

http://www.postgresql.org/docs/9.3/static/queries-with.html

在外部查询中，将执行。

例如：

WITH enrollment AS (
SELECT c_id, c_title, c_enrolcap,
(SELECT COUNT(e_id) AS enrol FROM enrollments WHERE e_c_id = c_id) AS enrolled

FROM classes AS c)
SELECT c_id, c_title, c_enrolcap, enrolled, c_enrolcap - enrolled AS avail
FROM enrollment

- Eric Hauenstein