时间戳

首页 > 解决方案 > 如何编写查询/创建视图以限制多条记录仅显示最大值

sql - 如何编写查询/创建视图以限制多条记录仅显示最大值

问题描述

考虑以下三个表。联系人列表、具有定义“等级”的状态列表以及将联系人链接到多个状态的连接表。

   CREATE TABLE public."Contacts"
   (
     name character varying COLLATE pg_catalog."default",
     email character varying COLLATE pg_catalog."default",
     contactid integer NOT NULL DEFAULT nextval('"Contacts_contactid_seq"'::regclass),
     CONSTRAINT "Contacts_pkey" PRIMARY KEY (contactid)
   )
   CREATE TABLE public.statusoptions
   (
     option character varying COLLATE pg_catalog."default" NOT NULL,
     "Rank" integer,
     CONSTRAINT "ListOptions_pkey" PRIMARY KEY (option)
   )

   CREATE TABLE public."ContactStatus"
   (
     contactid integer NOT NULL,
     option character varying COLLATE pg_catalog."default" NOT NULL,
     CONSTRAINT "Options_pkey" PRIMARY KEY (contactid, option),
     CONSTRAINT fk_1 FOREIGN KEY (contactid)
      REFERENCES public."Contacts" (contactid) MATCH SIMPLE
      ON UPDATE NO ACTION
    ON DELETE NO ACTION,
    CONSTRAINT fk_2 FOREIGN KEY (option)
    REFERENCES public.statusoptions (option) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    )

以下查询返回所有行。

  select "Contacts".contactid, "Contacts".name, "ContactStatus".option, statusoptions."Rank" as 
  currentRank
  from "Contacts","ContactStatus", statusoptions
  where "Contacts".contactid = "ContactStatus".contactid
   and statusoptions.option="ContactStatus".option

这将返回一个如下所示的记录集：

          Contactid name        Status          CurrentRank
           1        "john"      "apply"             1
           1        "john"      "Manager Review"    4
           2        "bill"      "apply"             1
           2        "bill"      "1st interview"     2
           1        "john"      "1st interview"     2

我需要的是创建一个查询/视图，它总是只返回 MAX 当前 RANK 的行。所以我想从这个视图中得到的预期结果是：

         Contactid      name        Status          CurrentRank
          1           "john"        "Manager Review"    4
          2           "bill"        "1st interview"     2

在任何时候，我都可以更改 statusoptions 字段中的“Rank”值，这将相应地更改视图。

这可能吗？

标签： sqlpostgresqlgreatest-n-per-group

解决方案

您可以在以下位置使用 distinct：

select distinct on(c.contactid)
    c.contactid, 
    c.name, 
    cs.option, 
    s."Rank" as currentRank
from 
    "Contacts" c
    inner join "ContactStatus" cs on c.contactid = cs.contactid
    inner join statusoptions s on s.option = cs.option
order by c.contactid, s."Rank" desc

笔记：

  • 始终使用显式标准连接（带有on子句）而不是老式的隐式连接（带有逗号where）

  • （短）表别名使查询更短且更易于阅读

  • 考虑避免引用表名和列名，除非绝对必要；它们使标识符区分大小写，而默认情况下它们不是

推荐阅读