使用SQLite窗口功能并创建最新的SQLite版本数据库 [英] Using SQLite window functions and creating up-to-date SQLite version database
问题描述
我无法使用SQLite窗口功能(例如LAG，LEAD).经过一些调查，这似乎是由于SQLite数据库版本低于3.25.0，此后才提供了窗口功能.
I'm unable to utilize SQLite window functions (e.g. LAG, LEAD). After some investigation it seems that this is due to the fact that SQLite database version is below 3.25.0, after which window functions were made available.
当测试包含窗口函数的查询时，例如 sqlitetutorial网站:显示以下错误
When queries which include window functions are tested, such as the ones provided on sqlitetutorial site: the following errors are displayed
请问您如何解决此问题.非常感谢您的支持.
Could you please advise how to resolve this. I would much appreciate your support on this.
SELECT CustomerId, Year, Total,
LAG (Total, 1, 0) OVER (ORDER BY Year) PreviousYearTotal
FROM CustomerInvoices
WHERE CustomerId = 4;
SELECT CustomerId, Year, Total,
LAG (Total, 1, 0) OVER (ORDER BY Year) PreviousYearTotal
FROM CustomerInvoices
WHERE CustomerId = 4;
推荐答案
发生了同样的问题.看来您正在使用SQLiteStudio，最新版本为2019年12月，为3.21.虽然您的数据库是在SQLite 3.30中创建的(并且您的软件可能正在使用3.30)，但您使用的SQLiteStudio的版本却基于3.24.
Had this same issue. It looks like you're using SQLiteStudio, the latest version is 3.21 from December 2019. While your database was made in SQLite 3.30 (and your software is probably using 3.30), the version of SQLiteStudio you are using is based on 3.24.
有一个杰出的功能请求可以在SQLiteStudio中实现窗口功能.在此之前，您所能做的最好的就是将SQLiteStudio程序目录中的sqlite3.dll替换为较新的版本(3.30，32位版本).窗口函数将显示为语法错误，但查询仍将执行.
There is an outstanding feature request to implement window functions in SQLiteStudio. Until then, the best you can do is replace the sqlite3.dll in the SQLiteStudio program directory with a newer version (3.30 , 32-bit works). Window functions will show as a syntax error, but the queries will still execute.
这篇关于使用SQLite窗口功能并创建最新的SQLite版本数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
