横向查询语法

Question

我正试图获得横向工作在Postgres 9.5.3查询。

select  b_ci."IdOwner",
ci."MinimumPlaces",
ci."MaximumPlaces",
(select count(*) from "LNK_Stu_CI" lnk
where lnk."FK_CourseInstanceId" = b_ci."Id") as "EnrolledStudents",
from "Course" c
join "DBObjectBases" b_c on c."Id" = b_c."Id"
join "DBObjectBases" b_ci on b_ci."IdOwner" = b_c."Id"
join "CourseInstance" ci on ci."Id" = b_ci."Id",
lateral (select ci."MaximumPlaces" - "EnrolledStudents") x

我希望最右边的列是"MaximumPlaces"-"enrollment ledstudents"的结果，但我很难让它工作。目前PG正在抱怨"注册学生"不存在——这正是"横向"的意义，不是吗?

select  b_ci."IdOwner",
ci."MinimumPlaces",
ci."MaximumPlaces",
(select count(*) from "LNK_Stu_CI" lnk
where lnk."FK_CourseInstanceId" = b_ci."Id") as "EnrolledStudents",
lateral (select "MaximumPlaces" - "EnrolledStudents") as "x"
from "Course" c
join "DBObjectBases" b_c on c."Id" = b_c."Id"
join "DBObjectBases" b_ci on b_ci."IdOwner" = b_c."Id"
join "CourseInstance" ci on ci."Id" = b_ci."Id"

如果我尝试在select中内联横向子句(如上所示)，它也会心烦意乱，并给我一个语法错误-那么它去哪里?

谢谢,亚当。

Answer 1

你没有抓住LATERAL的要点。它可以访问FROM子句中表中的列，但不能访问SELECT子句中定义的别名。

如果要访问SELECT子句中定义的别名，则需要添加另一个查询级别，可以使用FROM子句(AKA派生表)中的子查询，也可以使用CTE (Common table Expression)。由于PostgreSQL中的CTE充当了一个优化栅栏，我强烈建议在这种情况下使用子查询，如:

select
    -- get all columns on the inner query
    t.*,
    -- get your new expression based on the ones defined in the inner query
    t."MaximumPlaces" - t."EnrolledStudents" AS new_alias
from (
    select  b_ci."IdOwner",
    ci."MinimumPlaces",
    ci."MaximumPlaces",
    (select count(*) from "LNK_Stu_CI" lnk
    where lnk."FK_CourseInstanceId" = b_ci."Id") as "EnrolledStudents",
    from "Course" c
    join "DBObjectBases" b_c on c."Id" = b_c."Id"
    join "DBObjectBases" b_ci on b_ci."IdOwner" = b_c."Id"
    join "CourseInstance" ci on ci."Id" = b_ci."Id"
) t