Does SQLite support common table expressions?
SQLite是否支持公用表表达式？
I'd like to run query like that:
我想运行这样的查询：
with temp (ID, Path)
as (
select ID, Path from Messages
) select * from temp
3 个解决方案
#1
12
As of Sqlite version 3.8.3 SQLite supports common table expressions.
从Sqlite版本3.8.3开始，SQLite支持公用表表达式。
更改日志
说明
#2
1
Another solution is to integrate a "CTE to SQLite" translation layer in your application :
另一种解决方案是在您的应用程序中集成“CTE到SQLite”转换层：
"with w as (y) z" => "create temp view w as y;z"
“用w作为（y）z”=>“创建临时视图w为y; z”
"with w(x) as (y) z" => "create temp table w(x);insert into w y;z"
“用w（x）作为（y）z”=>“创建临时表w（x）;插入到w y; z”
As an (ugly, desesperate, but working) example : http://nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb
作为一个（丑陋，缺乏，但有效）的例子：http：//nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb
#3
0
SQLite doesn't support CTEs, window functions, or any of the like. You can, however, write your own user functions that you can call inside SQLite by registering them to the database with the SQLite API using sqlite_create_function(). You register them with the database, and then you can use them in your own application code. You can make an aggregate function that would perform the sum of a series of averages based on the individual column values. For each value, a step-type callback function is called that allows you to perform some calculation on the data, and a pointer for holding state data is also available.
SQLite不支持CTE，窗口函数或任何类似的东西。但是，您可以编写自己的用户函数，可以使用SQLite API使用sqlite_create_function（）将它们注册到数据库中。您可以在数据库中注册它们，然后可以在自己的应用程序代码中使用它们。您可以创建一个聚合函数，该函数将根据各个列值执行一系列平均值的总和。对于每个值，调用步骤类型回调函数，该函数允许您对数据执行某些计算，并且还可以使用用于保存状态数据的指针。
#1
12
As of Sqlite version 3.8.3 SQLite supports common table expressions.
从Sqlite版本3.8.3开始，SQLite支持公用表表达式。
更改日志
说明
#2
1
Another solution is to integrate a "CTE to SQLite" translation layer in your application :
另一种解决方案是在您的应用程序中集成“CTE到SQLite”转换层：
"with w as (y) z" => "create temp view w as y;z"
“用w作为（y）z”=>“创建临时视图w为y; z”
"with w(x) as (y) z" => "create temp table w(x);insert into w y;z"
“用w（x）作为（y）z”=>“创建临时表w（x）;插入到w y; z”
As an (ugly, desesperate, but working) example : http://nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb
作为一个（丑陋，缺乏，但有效）的例子：http：//nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb
#3
0
SQLite doesn't support CTEs, window functions, or any of the like. You can, however, write your own user functions that you can call inside SQLite by registering them to the database with the SQLite API using sqlite_create_function(). You register them with the database, and then you can use them in your own application code. You can make an aggregate function that would perform the sum of a series of averages based on the individual column values. For each value, a step-type callback function is called that allows you to perform some calculation on the data, and a pointer for holding state data is also available.
SQLite不支持CTE，窗口函数或任何类似的东西。但是，您可以编写自己的用户函数，可以使用SQLite API使用sqlite_create_function（）将它们注册到数据库中。您可以在数据库中注册它们，然后可以在自己的应用程序代码中使用它们。您可以创建一个聚合函数，该函数将根据各个列值执行一系列平均值的总和。对于每个值，调用步骤类型回调函数，该函数允许您对数据执行某些计算，并且还可以使用用于保存状态数据的指针。