时间戳

首页 > 解决方案 > 如何使用 Lumen 计算 PostgreSQL 中的数据在字段中显示 count_apply

sql - 如何使用 Lumen 计算 PostgreSQL 中的数据在字段中显示 count_apply

问题描述

我想再次询问有关带有框架 Lumen 的 PostgreSQL。我在同一个表中有一个带有实现克隆的表。对于差异，我将 parent_id 用于克隆行，id 上的真实数据引用。我计算另一个表中的数据，但只计算克隆数据而不是真实数据。

这是结果查询的图像：

结果查询

克隆数据有 count_apply 但我希望 count_apply 也显示在真实数据中。我想要这样的结果：

图片

如何查询该数据？这是我的代码。

select
    "requisitions"."id",
    "requisitions"."title",
    "requisitions"."parent_id",
    (select count(id) from requisition_users where requisition_id = requisitions.id) AS count_apply,
    "requisitions"."status"
from
    "requisitions"
    inner join "users" on "requisitions"."created_by" = "users"."id"
    inner join "companies" on "requisitions"."company_id" = "companies"."id" 
    -- inner join "requisition_users" on "requisitions"."id" = "requisition_users"."requisition_id"
where
    "requisitions"."entity_id" = 1 
    -- and "requisitions"."parent_id" is not null
    -- and "requisitions"."status" >= 5
    -- and (select count(id) from requisition_users where requisition_id = requisitions.id) > 0
    -- and "requisitions"."parent_id" = "requisitions"."id"
    and "requisitions"."deleted_at" is null

标签： sqlpostgresqllumen

解决方案

由于我不知道您的表的数据结构，因此我编写了一个外部查询来获得您想要的结果。请试试这个，如果您遇到任何困难，请告诉我：

select   
    "id",
    "title",
    "parent_id",
    (case when count_apply>0 then count_apply else max(count_apply)over(partition by "title") end)count_apply,
    "status" 
from
(select
    "requisitions"."id",
    "requisitions"."title",
    "requisitions"."parent_id",
    (select count(id) from requisition_users where requisition_id = requisitions.id) AS count_apply,
    "requisitions"."status"
from
    "requisitions"
    inner join "users" on "requisitions"."created_by" = "users"."id"
    inner join "companies" on "requisitions"."company_id" = "companies"."id" 
    -- inner join "requisition_users" on "requisitions"."id" = "requisition_users"."requisition_id"
where
    "requisitions"."entity_id" = 1 
    -- and "requisitions"."parent_id" is not null
    -- and "requisitions"."status" >= 5
    -- and (select count(id) from requisition_users where requisition_id = requisitions.id) > 0
    -- and "requisitions"."parent_id" = "requisitions"."id"
    and "requisitions"."deleted_at" is null)t

推荐阅读