postgresql - Postgresql - CTE 加入 cte 并排序

Question

我的 sql 文件中有两个 CTE：

• 第一个在库存中找到电影的总成本。如果库存中有 8 份 DVD 电影，每份成本 10 美元，那么它输出 80 作为总成本。
• 第二个找到电影的总收入。如果一部电影被租了 20 次，每次租金是 5 美元，那么它输出 100 作为收入。
• 然后使用这些 CTE，我需要通过计算 total_revenue - total_cost 找到最赚钱的 10 部电影。total_cost 和 total_revenue 有效，但当我加入它们时它不起作用。存在语法错误。

    /*TOTAL COST OF FILMS */

WITH total_cost AS (
  select  "films"."filmId",
          "films"."title",
          count("inventory"."inventoryId") as "numberOfCopies",
          sum("films"."replacementCost") as "totalCost"
  from    "films"
  join    "inventory" using ("filmId")

  group by ("films"."filmId");
  order by "totalCost" desc
  limit 10;
)

/* TOTAL REVENUE FROM FILMS */
WITH total_revenue AS (
  select    "films"."filmId",
            "films"."title",
            count("rentals"."rentalId") as "numberOfRentals",
            sum ("films"."rentalRate") as "totalRevenue"
  from    "films"
  join    "inventory" using ("filmId")
  join    "rentals" using ("inventoryId")

  group by ("films"."filmId");
  order by "totalRevenue" desc
  limit 10;
)

  select    "films"."filmId",
            "films"."title",
            "total_revenue"."totalRevenue" - "total_cost"."totalCost" as "profit"
  from "films"
  join "total_cost" using ("filmId")
  join "total_revenue" using ("filmId")
  group by "films"."filmId"
  order by "profit" desc
  limit 10;

标签： postgresqlcommon-table-expression