Query

有重複日期時僅選擇 Max(date)

  • May 16, 2019

這是我的Tracking表中包含的數據範例:

tracking_id   date_action  action_id    staffing_id
------------- -----------  -----------  -----------
1             2019/03/04  4             2
2             2019/03/04  3             2
7             2018/10/25  67            3
4             2018/10/25  8             3
5             2019/05/05  10            5
6             2019/05/05  6             5
8             2019/03/02  55            6

我想要的結果如下:

 tracking_id   date_action  action_id    staffing_id
   ------------- -----------  -----------  -----------
    2             2019/03/04  3             2
    4             2018/10/25  8             3
    6             2019/05/05  6             5
    8             2019/03/02  55            6

我需要找到max(date_action_taken) group by staffing_id並將其與其他表連接,以使用 action_id 獲取 Action 表中的列,並Staffing使用staffing_id.

我首先嘗試獲取Tracking每個表中的最大日期staffing_id

SELECT staffing_id, Max(Tracking.date_action_taken) AS MaxOfdate
FROM Tracking
GROUP BY staffing_id

當我將上面的查詢加入同一個表以獲取跟踪表的其他列時。它仍然返回,max(date_action)但它也返回重複的。我使用的查詢如下:

Select  tracking_id, t2.mxdate, t1.action_id, t1.staffing_id
FROM Tracking t1
inner join
(select  max(date_action) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

我什至嘗試了另一個查詢來max(tracking_id)使用這個查詢來代替:

Select Tracking.*
From Tracking 
Where tracking_id IN
(Select Max(tracking_id) as t_id
From Tracking
Group by staffing_id
)

在我意識到max(tracking)不一定是max(date_action).

我什至使用了 DISTINCT。它仍然返回重複的max(date_action).

Select  DISTINCT tracking_id, t2.mxdate, t1.action_id, t1.comment, t1.staffing_id
FROM Tracking t1
inner join
(select  DISTINCT max(date_action_taken) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

我不知道該怎麼辦。有沒有辦法,我可以先通過 staffing_id 獲取,如果重複其中兩個max(date_action)來獲取.max(tracking_id)``max(date_action)

我正在考慮添加另一列作為“標誌”。跟踪表中每個插入的數據類型yes/no、列名為(isTheLastAction)so。它找到前一個 isTheLastAction,將其設置為false,並將新插入為true,以同樣staffing_id的方式,它不依賴於date_action也不依賴於tracking_id。我只是不知道該怎麼做。

max(date_action)關於幫助我解決或在插入時使用標誌的任何想法?

Akina 已經指出了數據的關鍵問題……沒有現有的方法來確定跟踪行的正確順序。跳過這個問題是很有誘惑力的,但儘管存在問題,這裡還是有一些概念和 SQL 查詢模式可以提供幫助。

  1. 在傳統的關係數據庫中,不應該假設記錄的*儲存順序。*儘管按添加順序儲存記錄可能很自然,但實際上並不能保證這一點。數據庫文件可以分段,然後再壓縮,等等,並且行可能不會以相同的順序重建。儘管 Access 通常可以按特定順序儲存記錄,但依賴任何此類插入或儲存順序都是錯誤的。
  • 範例[Tracking]數據通過顯示[tracking_id] = 7before來暗示給定的順序[tracking_id] = 4,並且註釋進一步表明一個是在另一個之前插入的。這種錯誤的假設可能會引起一些混亂。
  • SQL 的一個相關原則是應該假定查詢行是按特定順序排列的,除非指定了 ORDER BY 子句。再一次,僅僅因為它通常與插入順序相匹配而依賴預設順序是錯誤的。
  • 可以使用各種約束和索引的規範來促進有效的排序。例如,如果在表上指定了一個主鍵,則很可能這些行將由該主鍵儲存,但它們很可能儲存在分段組/模式中以方便快速搜尋。期望 SQL 查詢總是以主鍵順序返回行而沒有顯式的 GROUP BY 子句仍然是錯誤的。
  1. 至少在 Access 中,可以通過使用長整數自動編號列來實現邏輯順序。Access 會跟踪使用的自動編號值,並將始終為新行分配一個唯一編號。在這種情況下,可以相信在較低的數字之後插入了較高的數字。(注意:這種行為並非普遍適用,因為預設情況下某些數據庫系統將重用已刪除記錄的數量。)
  • 對於表來說,使用自動編號列是一種常見的做法,因此很自然地假設這[tracking_id]是這樣一個列。大多數使用者在第一次閱讀時會假設[tracking_id] = 7是 AFTER [tracking_id] = 4老實說,它們沒有按這樣的順序這一事實令人困惑,尤其是沒有其他欄位來指示插入順序。
  • 另一種選擇是使用日期欄位,即“時間戳”欄位。如果此欄位的唯一原因是指示插入/創建順序,那麼我建議使用Default Value = Now(). (由於訪問日期的解析度只有一秒,這僅在插入記錄的速度不快於每秒的情況下才有效。)
  • 您所描述的“標誌”列是不鼓勵的,如果使用上述其他模式之一,則應該是不必要的。在插入和編輯時手動更改標誌值可能會成為維護的噩夢,並且如果沒有適當的約束(驗證規則和/或唯一索引),它可能會導致相互矛盾的數據。

對於以下查詢,我將假設這[tracking_id]是一個唯一的 Autonumber 欄位,並且它始終表示插入順序,因此更高的數字是具有相同[date_action].

我假設每個[staffing_id]都有可能有多個[date_action]值。範例數據僅顯示每個 的一個日期(儘管多行)[staffing_id]。僅基於範例數據來簡化查詢是很誘人的,但這是一個錯誤,可能會返回不正確的聚合值。現有的兩個答案犯了這個錯誤。

問題已經包含解決方案的所有各個部分,但從未將它們組合起來以產生完整的解決方案。 錯誤在於假設只需一個聚合子查詢就可以達到預期的結果。 實際上,需要有兩個子查詢分別找到最大日期和最大跟踪ID!(老實說,問題文本使用單詞概述了以下三個步驟,但隨後無法匹配查詢中的邏輯。)

  • [date_action]查詢1:首先獲得給定的最大值[staffing_id]
  • 查詢 2:然後獲取查詢 1 中找到[tracking_id] 的最大日期和 id 的最大值。(在知道最大日期之前,您無法[tracking_id]單獨或同時找到最大值——在同一個聚合查詢中。)
  • 查詢 3:最後通過連結到找到的最大值來獲取記錄詳細資訊(其他欄位)[tracking_id]。(因為staffing_idanddate_action已經在內部查詢中被選中,並且因為tracking_id被認為是唯一鍵,所以只需要對該鍵值進行最終選擇。換句話說,在最外層的 ON 子句中包含其他欄位是多餘的。 )

SELECT t1.*
FROM Tracking AS t1
 INNER JOIN (
   SELECT Max(t2.tracking_id) AS MaxOfTracking_id
   FROM Tracking AS t2
     INNER JOIN (
       SELECT t3.staffing_id, Max(t3.date_action) AS MaxOfdate
       FROM Tracking AS t3
       GROUP BY t3.staffing_id
     ) AS tad
     ON t2.staffing_id = tad.staffing_id AND t2.date_action = tad.MaxOfdate
   GROUP BY t2.staffing_id, t2.date_action
 ) AS tai
 ON t1.tracking_id = tai.MaxOfTracking_id
ORDER BY t1.staffing_id

回報:

tracking_id   date_action  action_id  staffing_id
2             3/4/2019     3          2
7             10/25/2018   67         3
6             5/5/2019     6          5
8             3/2/2019     55         6

引用自:https://dba.stackexchange.com/questions/238225