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

Question

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

   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

我需要的是创建一个查询/ View ，它总是只返回 MAX 当前排名的行。所以我希望从这个 View 中得到的预期结果是:

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

我可以随时更改 statusoptions 字段中的“Rank”值，这会相应地更改 View 。

这可能吗？

Answer 1

你可以使用不同的:

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 子句中的逗号)

• (短)表别名使查询更短且更易于阅读

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