問題描述

mysql> select * from FinalTable;
+------+-------+-------+---------------------+
| id   | name  | state | timestamp           |
+------+-------+-------+---------------------+
|   12 | name1 | TX    | 2020-01-25 11:29:36 |
|   14 | name3 | CA    | 2020-01-25 11:29:36 |
|   14 | name3 | TX    | 2020-01-25 11:29:36 |
|   12 | name1 | CA   | 2020-01-25 11:29:36 |
|   13 | name2 | TA   | 2020-01-25 11:29:36 |
|   14 | name3 |  CA   | 2020-01-25 11:29:36 |
+------+-------+-------+---------------------+

我正在查看給出響應的輸出查詢:

I am looking at output query which gives response as:

I2 name1 TX 2020-01-25 11:29:36  CA 2020-01-25 11:29:36

當我運行查詢時，

select id,name,state,timestamp,
lead(state,1) over (partition by id order by timestamp asc) out_state,
lead(timestamp,1) over (partition by id order by timestamp asc) out_timestamp
from FinalTable

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by id order by timestamp asc) out_state,
lead(timestamp,1) over (part' at line 2

還可以在數據庫中創建高達毫秒而不是秒的時間戳嗎?我正在使用 CURRENT_TIMESTAMP.

also is it possible to create timetamp upto milliseconds instead of seconds in DB? I am using CURRENT_TIMESTAMP.

推薦答案

窗口函數(例如 lead())僅在 MySQL 8.0 中添加，因此它們在 5.7 版中不可用.您可以像這樣使用自連接來模擬 lead():

Window functions (such as lead()) were added in MySQL 8.0 only, so they are not available in version 5.7. You can emulate lead() with a self-join like so:

select t.*, tlead.state, tlead.timestamp
from FinalTable t
left join FinalTable tlead 
    on tlead .id = t.id
    and tlead.timestamp = (
        select min(t1.timestamp) 
        from FinalTable t1 
        where t1.id = t.id and t1.timestamp > t.timestamp
    )

旁注:為了使該方法正常工作，您需要相同id的后續記錄具有不同的timestamp - 這在示例數據中并非如此您展示的所有時間戳都相同(我認為這是您的示例數據中的拼寫錯誤).

Side note: for this method to work properly, you need subsequent records of the same id to have different timestamps - which is not the case in the sample data that you showed, where all timestamps are the same (I assume this is a typo in your sample data).

這篇關于ERROR 1064 (42000) 過度分區語法中的數據庫錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！