sql - 创建 View ，选择与另一列相关的一列中的最大值

Question

我需要在 PostgreSQL 9.4 中创建一个关于这个表的 View :

CREATE TABLE DOCTOR (
    Doc_Number INTEGER,
    Name    VARCHAR(50) NOT NULL,
    Specialty   VARCHAR(50) NOT NULL,
    Address VARCHAR(50) NOT NULL,
    City    VARCHAR(30) NOT NULL,
    Phone   VARCHAR(10) NOT NULL,
    Salary  DECIMAL(8,2) NOT NULL,
    DNI     VARCHAR(10) NOT NULL,
    CONSTRAINT pk_Doctor PRIMARY KEY (Doc_Number)
  );

该 View 将显示每个 专业 中具有最高 薪水 的医生的排名，我试过这段代码，但它显示了每个专业的所有医生:

CREATE VIEW top_specialty_doctors 
AS (Select MAX(Salary), name, specialty from DOCTOR
    where specialty = 'family and community'
    or specialty = 'psychiatry'
    or specialty = 'Rheumatology'
    group by name, salary, specialty);

View 只显示每个专业薪水最高的医生，我该怎么做。

Answer 1

DISTINCT ON 是一种简单的 Postgres 特定技术，可以在每组中获得 一个 获胜者。详情:

• Select first row in each GROUP BY group?

CREATE VIEW top_specialty_doctors AS 
SELECT DISTINCT ON (specialty)
       salary, name, specialty
FROM   doctor
WHERE  specialty IN ('family and community', 'psychiatry', 'Rheumatology')
ORDER  BY specialty, salary DESC, doc_number  -- as tiebreaker

而且您不需要在 CREATE VIEW 的查询周围需要括号.

如果多个文档并列获得最高薪水，则选择具有最小doc_number 的文档。

如果salary可以为NULL，使用DESC NULLS LAST:

• PostgreSQL sort by datetime asc, null first?

对于大表和某些数据分布，其他查询技术更胜一筹:

• Optimize GROUP BY query to retrieve latest record per user