MySQL - 當日期和時間是兩個欄位時獲取最新記錄

我有一個數據庫，其中日期和時間是兩列。我需要獲取最新的（最新的我是指時間最長的記錄）行。這用作子查詢來提供另一個查詢。

D B

id      date       time    currentOdometer    totalOdometer       vno
1   2020-03-21    10:00      250                  3001          ABCD
2   2020-03-21    09:59      249                  3000          ABCD          
3   2020-03-21    10:01      251                  3002          ABCD
4   2020-03-21    10:02      252                  3003          ABCD

我的要求是，當我得到一條新的 vno ABCD 記錄時，我需要找到最新的記錄並操作數據。

例如，當我在 10:03 獲得一條新記錄時，我需要找到最新的記錄，即 10:02 和值。

我的查詢是

(
SELECT Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
 SELECT MAX(timeStamp(X.date,X.time)) 
 FROM tbl X WHERE vno= 'ABCD') AND vno= 'ABCD'
)

此查詢是這樣的 INSERT 語句的一部分

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
 SELECT MAX(timeStamp(X.date,X.time)) from tbl X 
 WHERE vno= 'ABCD') AND vno= 'ABCD'),3003,'ABCD'
)

當我以這種方式執行查詢時，我得到子查詢返回多行的錯誤。

我在每個 select statemtn 中使用 LIMIT 1 重新建構了查詢，然後在

ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
ER_LOCK_DEADLOCK
undefined

對於這樣的問題，CREATE TABLE 語句和用於範例數據的 INSERT 語句確實很有用。更好的是小提琴或類似的。現在，我所能做的就是給你一些想法，但我無法測試它。

查看您的查詢：

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
 SELECT MAX(timeStamp(X.date,X.time)) from tbl X 
 WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

子選擇可能有一些語法錯誤（不平衡的“）”，或者至少它們在錯誤的地方），但是您不應該將 VALUES 子句與子選擇一起使用，最好重新編寫像：

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
 SELECT MAX(timeStamp(X.date,X.time)) from tbl X 
 WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

作為旁注，避免使用保留字作為標識符（日期、時間、時間戳）。

WHERE 子句也看起來不對，我假設 3003 和 ‘ABCD’ 是 totalOdometer、vno 的值。你是這個意思嗎？

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer) 
     , 3003, 'ABCD'
FROM tbl Y 
WHERE timeStamp(date,time) = (
 SELECT MAX(timeStamp(X.date,X.time)) 
 from tbl X 
 WHERE vno= 'ABCD' 
   AND IMEI = 'ABCD'
);

我為類似項目找到的唯一解決方案是對數據庫進行一點非規範化。

沿著包含所有 vno 的每個點的表，您必須創建相同結構的輔助表，除了沒有ID列並且vno列是主鍵。在每次插入主表時，您必須INSERT ... ON DUPLICATE KEY UPDATE進入輔助表，其中僅儲存每個 vno 的最後接收值。當然，您必須驗證新收到的日期和時間是否大於現有日期和時間。在某些情況下，一些跟踪器可以以任意順序發送 NMEA。

這種方法有一點成本（兩個插入而不是一個），但可以避免 MAX() 計算的耗時分組。事實上，最好將插入和驗證都包裝到事務中。

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