时间戳

首页 > 解决方案 > 如何通过查询从组中的多个表中进行选择？

sql - 如何通过查询从组中的多个表中进行选择？

问题描述

我有一些数据库表，其中包含人们需要签署的一些文件。这些表的定义（有些简化）如下。

create table agreement (
    id integer NOT NULL,
    name character varying(50) NOT NULL,
    org_id integer NOT NULL,
    CONSTRAINT agreement_pkey PRIMARY KEY (id)
    CONSTRAINT org FOREIGN KEY (org_id) REFERENCES org (id) MATCH SIMPLE
)

create table version (
    id integer NOT NULL,
    content text NOT NULL,
    publish_date timestamp NOT NULL,
    agreement_id integer NOT NULL,
    CONSTRAINT version_pkey PRIMARY KEY (id)
    CONSTRAINT agr FOREIGN KEY (agreement_id) REFERENCES agreement (id) MATCH SIMPLE
)

我跳过了 org 表，以减少混乱。我一直在尝试编写一个查询，该查询将为我提供给定组织的所有正确协议信息。到目前为止，我可以做到

SELECT a.id, a.name FROM agreement AS a
JOIN version as v ON (a.id = v.agreement_id)
JOIN org as o ON (o.id = a.org_id)
WHERE o.name = $1
GROUP BY a.id

对于属于我想要的组织并且至少有一个版本的每个协议，这似乎给了我一个单一的记录。但我还需要包括可用的最新版本的内容和发布日期。我怎么做？

另外，我有一个单独的表，称为签名，它链接到用户和版本。如果可能，我想将此查询扩展为仅包含给定用户尚未签署最新版本的协议。

编辑：反映了 org join 的需要，因为我按名称而不是 id 选择 orgs

标签： sqlpostgresql

解决方案

您可以使用相关子查询：

SELECT a.id, a.name, v.*
FROM agreement a JOIN
     version v
     ON a.id = v.agreement_id
WHERE a.org_id = $1 AND
      v.publish_date = (SELECT MAX(v2.publish_date) FROM version v2 WHERE v2.agreement_id = v.agreement_id);

笔记：

  • 该org表不需要，因为agreement有一个org_id.
  • 此查询不需要聚合。您正在过滤最近的记录。
  • 相关子查询是检索最新版本的一种方法。

推荐阅读