加入三個具有最大日期值的表

我正在嘗試加入三個表：

hydrants
-----------------
fid | h_number | 
-----------------
1  | 2525     |
-----------------

hydrats_survey
----------------------------------------------
fid | survey_date | condition | hydrants_fid|
----------------------------------------------
 1 |  2020-02-12 | good      | 1           |
----------------------------------------------
 2 |  2020-02-15 | good      | 1           |
----------------------------------------------

hydrants_measurement
----------------------------------------------------
fid | measurement_date | condition | hydrants_fid |
----------------------------------------------------
1  | 2020-02-01       | bad       | 1            |
----------------------------------------------------
2  | 2020-02-05       | good      | 1            |
----------------------------------------------------

我正在嘗試創建一個表，其中消防栓只有一個日期和一個條件，但我只需要來自 hydrats_survey 或 hydrants_measurement 的最後一個（最大）日期。像這樣：

hydrants_join_table
----------------------------------------------
fid | h_number | last_date | condition | 
----------------------------------------------
 1  |  2525    |  2020-02-15 | good      |   <---- it's last date from hydrats_survey and hydrants_measurement where hydrant_fid=1
----------------------------------------------

編輯：第三張表做了一點改動：

消火栓測量

-----------------------------------------------------------------------------
fid | measurement_date | condition | st_pressure | dyn_pressure |hydrants_fid |
-----------------------------------------------------------------------------
1  | 2020-02-01       | bad       | 10          | 0,5          |   1         |
-----------------------------------------------------------------------------
2  | 2020-02-05       | good      | 15          | 0,8          |   1         |
-----------------------------------------------------------------------------

情況幾乎相同，嘗試在最後一次測量或調查的位置創建一個表，但如果是測量，則應出現其他欄位（st_pressure 和 dyn_pressure）。

您可以使用UNION運算符將您的表hydrats_survey和hydrants_measurement表合併為一個數據集，然後您可以使用視窗函式通過如下查詢從該數據集中ROW_NUMBER()獲取最新的行：hydrant

SELECT hydrants.fid, hydrants.h_number, conditions.hydrant_condition_date as last_date, conditions.condition, conditions.st_pressure, conditions.dyn_pressure
FROM hydrants
INNER JOIN 
(
   SELECT fid, hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure, ROW_NUMBER() OVER (PARTITION BY hydrants_fid ORDER BY hydrant_condition_date DESC) AS SortId
   FROM 
   (
       SELECT fid, survey_date AS hydrant_condition_date, condition, hydrants_fid, CAST(NULL AS INT) AS st_pressure, CAST(NULL AS DECIMAL(10,2)) AS dyn_pressure
       FROM hydrats_survey

       UNION

       SELECT fid, measurement_date AS hydrant_condition_date, condition, hydrants_fid, st_pressure, dyn_pressure
       FROM hydrants_measurement
   )
) conditions
   ON hydrants.fid = conditions.hydrants_id
WHERE conditions.SortId = 1 -- Filters out everything but the latest record per hydrant ordered by date

唯一需要注意的是，如果您有兩個具有相同日期但在其他方面不同的記錄（例如 different conditions，正如 nbk 指出的那樣），這將隨機選擇這兩個記錄之一，因為沒有不同的欄位組合可以在裡面排序的ROW_NUMBER()功能。

有人可能會嘗試通過使用和表的fid列來解決這個問題，但是這兩個欄位彼此獨立，並且在子查詢中聯合在一起時仍然可能最終創建一個非唯一的數據集。所以你需要決定一個唯一的欄位作為決勝局（例如，如果在同一天有一個和，然後用 記錄）或者當有兩條記錄時接受結果將是半隨機的同一日期。hydrats_survey``hydrants_measurement``bad``good condition``bad condition

引用自：https://dba.stackexchange.com/questions/287219