获得明显的最高修订数

Question

背景

我很难编写一个查询，以便在3个表中为每个id选择一个具有最高修订号的记录。工作站点id在所有3个表之间共享，因此对worksitedraft.id = 103和worksitesubmission.id =103号的查询将以不同的状态返回相同的工作站点。

表格

所有3个表( worksite、worksiteDraft、worksiteSubmission )都有以下列：

id，名称，引用，主修正数，小修正数

本质上，我需要将所有三个表合并在一起，然后区分结果，但要确保每个结果都是三个表中修订次数最多的记录。

电流溶液

到目前为止，我有以下(工作)解决方案，但这是相当令人讨厌的，并认为必须有一个更好的方法。

在合并在一起的3个表中创建所有工作地点的视图：

CREATE VIEW allworksites AS
SELECT * FROM 
(
    SELECT id, name, reference, 'worksiteDraft' as type, CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
    FROM worksitedraft
    UNION
    SELECT id, name, reference, 'worksiteSubmission' as type, CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
    FROM worksitesubmission
    UNION
    SELECT id, name, reference, 'worksite' as type, CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
    FROM worksite
) as allworksites;

然后使用嵌套的select计算类型，方法是按主/次要修订编号排序，并将其限制为1。

SELECT * FROM allworksites
WHERE id = 106
AND type = 

(
    SELECT type FROM
    (
        SELECT
            id,
            type,
            revisionnumber
        FROM 
        (
            SELECT 'worksiteDraft' as type, CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
            FROM worksitedraft
            WHERE id = allworksites.id
            UNION ALL
            SELECT 'worksiteSubmission' as type, CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
            FROM worksitesubmission
            WHERE id = allworksites.id
            UNION ALL
            SELECT 'worksite' as type, CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
            FROM worksite
            WHERE id = allworksites.id
        ) as latestrevision
        ORDER BY revisionnumber DESC
        limit 1
    ) as latestrevisiontype
)

问题

还有另外一种(也许完全不同)更好的方法来查询这些数据吗？

编辑.根据请求添加了示例数据和所需的结果。

样本数据

工位

| "id"| "name"          | "reference"| "majorrevisionnumber" | "minorrevisionnumber"
| 101 | "Worksite One"  | "ref-1"    | 1                     | 0
| 102 | "Worksite Two"  | "ref-2"    | 1                     | 0
| 103 | "Worksite Three"| "ref-3"    | 1                     | 0
| 104 | "Worksite Four" | "ref-4"    | 2                     | 0
| 105 | "Worksite Five" | "ref-5"    | 2                     | 0
| 106 | "Worksite Six"  | "ref-6"    | 3                     | 0

工作地点

| "id"| "name"          | "reference"| "majorrevisionnumber" | "minorrevisionnumber"
| 101 | "Worksite One"  | "ref-1"    | 1                     | 1
| 102 | "Worksite Two"  | "ref-2"    | 1                     | 2
| 103 | "Worksite Three"| "ref-3"    | 1                     | 2
| 104 | "Worksite Four" | "ref-4"    | 1                     | 2
| 105 | "Worksite Five" | "ref-5"    | 2                     | 1
| 106 | "Worksite Six"  | "ref-6"    | 2                     | 2

工作现场

| "id"| "name"          | "reference"| "majorrevisionnumber" | "minorrevisionnumber"
| 101 | "Worksite One"  | "ref-1"    | 1                     | 2
| 102 | "Worksite Two"  | "ref-2"    | 1                     | 3
| 103 | "Worksite Three"| "ref-3"    | 1                     | 3
| 104 | "Worksite Four" | "ref-4"    | 1                     | 2
| 105 | "Worksite Five" | "ref-5"    | 2                     | 2
| 106 | "Worksite Six"  | "ref-6"    | 2                     | 3

期望结果

| "id"| "name"          | "reference"| "majorrevisionnumber" | "minorrevisionnumber"
| 101 | "Worksite One"  | "ref-1"    | 1                     | 2
| 102 | "Worksite Two"  | "ref-2"    | 1                     | 3
| 103 | "Worksite Three"| "ref-3"    | 1                     | 3
| 104 | "Worksite Four" | "ref-4"    | 2                     | 0
| 105 | "Worksite Five" | "ref-5"    | 2                     | 2
| 106 | "Worksite Six"  | "ref-6"    | 3                     | 0

注意:我正在使用PostgreSQL9.4

编辑：@Gordon_Linoff和@a_horse_with_no_name建议后的最终解决方案

SELECT DISTINCT ON (id) * 
FROM (SELECT id, name, reference, 'worksiteDraft' as type, majorrevisionnumber, minorrevisionnumber
      FROM worksitedraft
      UNION ALL
      SELECT id, name, reference, 'worksiteSubmission' as type, majorrevisionnumber, minorrevisionnumber
      FROM worksitesubmission
      UNION ALL
      SELECT id, name, reference, 'worksite' as type, majorrevisionnumber, minorrevisionnumber
      FROM worksite
     ) allworksites
ORDER BY id, ARRAY[majorrevisionnumber, minorrevisionnumber]::int[] DESC;

Answer 1

您可以使用一个查询来完成这一任务。我认为最简单的方法是DISTINCT ON

SELECT DISTINCT ON (id) * 
FROM (SELECT id, name, reference, 'worksiteDraft' as type,
             CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
      FROM worksitedraft
      UNION ALL
      SELECT id, name, reference, 'worksiteSubmission' as type,      
             CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
      FROM worksitesubmission
      UNION ALL
      SELECT id, name, reference, 'worksite' as type, 
             CONCAT(majorrevisionnumber, '.', minorrevisionnumber) as revisionnumber
      FROM worksite
     ) allworksites
ORDER BY id, revisionnumber DESC;

备注：

• 使用UNION ALL而不是UNION。UNION会导致删除重复值的开销，这是不必要的开销。
• 除非您的修订格式正确，否则连接它们可能不会返回最新值。(即1.9按排序顺序排在1.12之后)。然而，这是问题中使用的逻辑。
• DISTINCT ON根据id子句返回每个id一行，这是遇到的第一行。
• 一个视图是不必要的。

编辑:要修复修订编号排序，只需不要将它们连接起来：

SELECT DISTINCT ON (id) * 
FROM (SELECT id, name, reference, 'worksiteDraft' as type,
             majorrevisionnumber, minorrevisionnumber
      FROM worksitedraft
      UNION ALL
      SELECT id, name, reference, 'worksiteSubmission' as type,      
             majorrevisionnumber, minorrevisionnumber
      FROM worksitesubmission
      UNION ALL
      SELECT id, name, reference, 'worksite' as type, 
             majorrevisionnumber, minorrevisionnumber
      FROM worksite
     ) allworksites
ORDER BY id, majorrevisionnumber DESC, minorrevisionnumber DESC;