Postgresql 将几个左连接合并为一个

发布于04月25日

这是SQL请求

SELECT ot.id, ot.parent_id, ot.id_object_types AS object_type, ot.object_code, ot.name,
  u.login, u.last_seen, u.pass_upd, u.pass_default, ct.citizens_total, fl.downloaded as files_downloaded, fl.total AS files_total,
  ct0.type0, ct1.type1, ct2.type2 
  FROM public.object_tree AS ot
  LEFT JOIN admin.admin_user AS u ON ot.object_code = u.r8012

  LEFT JOIN (select object_code, count(uploaded)::int as total, count(downloaded)::int as downloaded 
  from web.files f group by object_code) as fl ON fl.object_code = ot.object_code

  LEFT JOIN (select distinct ct.code_obj AS object_code, count(ct.citizens_id)::int as citizens_total 
  from tbd.citizens_vk ct WHERE ct.actual = 1 group by ct.code_obj) as ct ON ct.object_code = ot.object_code 
  LEFT JOIN (select distinct ct.code_obj AS object_code, count(ct.citizens_id)::int as type0 
  from tbd.citizens_vk ct WHERE ct.type_db = '0' AND ct.actual = 1 group by ct.code_obj) as ct0 ON ct0.object_code = ot.object_code 

  LEFT JOIN (select distinct ct.code_obj AS object_code, count(ct.citizens_id)::int as type1 
  from tbd.citizens_vk ct WHERE ct.type_db = '1' AND ct.actual = 1 group by ct.code_obj) as ct1 ON ct1.object_code = ot.object_code

  LEFT JOIN (select distinct ct.code_obj AS object_code, count(ct.citizens_id)::int as type2 
  from tbd.citizens_vk ct WHERE ct.type_db = '2' AND ct.actual = 1 group by ct.code_obj) as ct2 ON ct2.object_code = ot.object_code

  WHERE ot.id IN ( $1:csv )
  ORDER BY ot.id

是否有可能减少加入tbd.citizen_vk表的数量？因为由于加入数量较多，客户端的请求需要很长时间来处理.

SELECT ot.id, ot.parent_id, ot.id_object_types AS object_type, ot.object_code, ot.name, u.login, u.last_seen, u.pass_upd, u.pass_default, ct.citizens_到tal, fl.downloaded as files_downloaded, fl.到tal AS files_到tal, ct.type0, ct.type1, ct.type2 FROM public.object_tree AS ot LEFT JOIN admin.admin_user AS u ON ot.object_code = u.r8012 AND ot.id IN ( $1:csv ) LEFT JOIN ( select object_code, count(uploaded)::int as 到tal, count(downloaded)::int as downloaded from web.files f group by object_code) AS fl USING(object_code) LEFT JOIN ( select ct.code_obj AS object_code, count(ct.citizens_id)::int as citizens_到tal, (count(ct.citizens_id)filter(where ct.type_db ='0'))::int AS type0, (count(ct.citizens_id)filter(where ct.type_db ='1'))::int AS type1, (count(ct.citizens_id)filter(where ct.type_db ='2'))::int AS type2, from tbd.citizens_vk ct WHERE ct.actual = 1 group by ct.code_obj) AS ct USING(object_code) ORDER BY ot.id

Postgresql 将几个左连接合并为一个

推荐答案

Postgresql相关问答推荐

使用多个分隔符拆分SQL

PostgreSQL：函数结果表内冲突(...)上的"；中的字段名称

Psql：Windows 10上奇怪的错误输出编码

Postgres查询按月和年分组的总计数

对表执行 DISABLE TRIGGER ALL 是否也会禁用 FK 指向该表？

Postgres 查询指向国外数据工作者的分区表比直接查询 fdw 慢很多倍

gorm 不生成字符串列

计算每行的多列中的非 NULL 元素

使用包含重复元素的数组 Select 重复值

带有初始数据的 docker postgres 不会在提交中持久化

从 PostgreSQL 中的时间戳获取日期

Postgres NOT IN (null) 没有结果

将属性添加到 Sequelize FindOne 返回的对象

如何正确索引多对多关联表？

在 postgresql 中将 bool 转换为 int

如何使用 Node.js 和 Postgresql 找到最后一个插入 ID？

使用 Hibernate 注释映射 PostgreSQL 串行类型

如何使用 PostgreSQL 在任何列中查找所有具有 NULL 值的行

PostgreSQL 多种认证方式

postgresql DB中唯一键的正确数据类型是什么？