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

2024-05-11 • 问答

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

   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字段中更改“排名”值，从而相应地更改视图。

这可能吗？

您可以在以下选项上使用不重复：

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子句的逗号）
（短）表别名使查询更短且更易于阅读
请考虑避免引用表名和列名，除非绝对必要；它们使标识符区分大小写，而默认情况下不是

在Postgres中，您可以使用distinct on

我想你想要

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

注意：

使用正确的，明确的，标准 JOIN语法。
从不在FROM子句中使用逗号。
表别名使查询更易于编写和阅读。
您应避免引用表名和列名。那只会使查询不必要地混乱。
distinct on通常比row_number()之类的替代方案具有更好的性能。

您可以对其余字段进行 max （排名）和分组

select c.contactid,max(so.rank) currentRank 

from Contacts c
join ContactStatus cs on c.contactid = cs.contactid
join StatusOptions so on so.option = cs.option

group by c.contactid,cs.option

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

Huaolivia 回答：如何编写查询/创建视图以限制多个记录以仅显示最大值

大家都在问