根據日期（或日期範圍）加入

我不確定我是否完全捏造了我的小型愛好數據庫的設計（無論如何我都不是 DBA），但我有一個這樣的表（主鍵是(staffid, effectivefrom)）：

staffid | target | effectivefrom
--------|--------|---------------
 1      |  6.0   | 2012-01-01
 2      |  6.0   | 2012-01-01
 3      |  6.0   | 2012-01-01
 1      |  7.0   | 2012-03-01

所以基本上，三名員工一開始的目標都是 6.0，但在 3 月，ID 為 1 的員工有一個新的目標，即 7.0。我想維護歷史目標，因為它與其他表中的其他數據相關。

我想有一個使用者定義的函式，它以日期為參數，並且該函式需要根據日期將上表與另一個表連接起來。假設以 2 月 1 日為日期呼叫該函式，我希望加入的結果包括顯示所有員工 6.0 的目標列。

像這樣的東西（我認為這行不通，因為之前可能有多行dateParameter）：

SELECT othertable.*, targets.target
    FROM othertable
    JOIN targets ON
        othertable.staffid = targets.staffid AND
        targets.effectivedate <= dateParameter;

如果我做了絕對的 DBA ’no-no’ 或者我是否只需要一些咖啡因，請告訴我。

這將非常混亂，因為您需要找到(staffid, effectivefrom)在dateParameter.

我要做的是添加另一個欄位effectiveTo，effectiveFrom然後當您為工作人員添加新目標時，在添加之前檢查是否有任何目前目標 ( effectiveTo IS NULL)，然後設置其結束日期到新的目標開始日期（可選：如果目前目標不止一個，則拋出錯誤）。

所以，表格看起來像：

staffID | target | effectiveFrom | effectiveTo
--------|--------|---------------|-------------
 1      |  6.0   | 2012-01-01    | 2012-03-01
 2      |  6.0   | 2012-01-01    | NULL
 3      |  6.0   | 2012-01-01    | NULL
 1      |  7.0   | 2012-03-01    | NULL

這使您的搜尋查詢看起來像這樣：

SELECT  othertable.*, targets.target
FROM    othertable
           INNER JOIN 
       targets ON othertable.staffid = targets.staffid
WHERE dateParameter > effectiveFrom AND (dateParameter <= effectiveTo OR effectiveTo IS NULL)

其潛在的缺點是數據不一致（即員工有重疊的目標） - 你可以很容易地編寫一個腳本來檢查這一點。我建議製作一個addTarget(staffID, target, fromDate)儲存過程並將所有檢查/更新邏輯包裝在其中。

（一些風格註釋 - 我稍微重寫了您的查詢以將連接條件與 where 子句分開。另外，我知道這只是一個範例，但這SELECT table.*是一個不好的習慣 - 您傳輸的數據比您需要的多得多.)

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