others - MySQL - 在MariaDB10.1.34中,无法使用WITH AS

74 1

下面是我尝试复制的例子查询:


WITH service_audit AS (


 SELECT


 id AS service_id


 , revision_from AS revision_from


 , revision_until AS revision_until


 FROM audit


 WHERE


 ( revision_from >= '2019-04-01 00:00:00'


 AND revision_from< '2019-09-30 23:59:59'


 )


 ORDER BY


 id, revision_from, revision_until


)


SELECT


 service_id


 revision_from,


 revision_until,


 LAG(service_id, 1) OVER (PARTITION BY service_id ORDER BY service_id, revision_from, revision_until) service_id_lag


FROM


 service_audit;



而这就是我得到的错误:

你的SQL语法有一个错误; check the manual that corresponds to your MariaDB server version for the right syntax to use near'service_audit as ( [42000] [1064] you have an error in your SQL syntax ; check the manual that corresponds to your MariaDB server version for the right syntax to use near'service_audit as ( SELECT id as servi'at line 1 Query is : with service_audit A. . .

可以使用以下方法复现问题:


WITH


 cte1 AS (SELECT 'a' as a, 'b' as b FROM dual),


 cte2 AS (SELECT 'c' as c, 'd' as d FROM dual)


SELECT b, d FROM cte1 JOIN cte2


WHERE cte1.a = cte2.c;



时间: 原作者:

71 0

在此查询中不需要CTE,只需将它转换为简单的SELECT


SELECT


 id service_id


 revision_from,


 revision_until,


 LAG(service_id, 1) OVER (


 PARTITION BY service_id ORDER BY service_id, revision_from, revision_until


 ) service_id_lag


FROM audit


WHERE revision_from >= '2019-04-01 00:00:00' AND revision_from< '2019-09-30 23:59:59'


ORDER BY id, revision_from, revision_until



原作者:
148 3

在MariaDB 10.2.1中引入了通用表达式。

这里 (谷歌搜索"mariadb with"中第一个结果就是它 )

原作者:
...