MySQL递归CTE表不存在 [英] MySQL Recursive CTE table does not exist

问题描述

我正在学习递归函数，因为我需要在几天的时间内每天提取一行.这是我当前的数据

I'm learning about recursive functions, Since I need to extract a row for each day in a range of days. This is my current data

+----+------------+------------+
| id |    from    |     to     |
+----+------------+------------+
|  1 | 09-20-2019 | 09-25-2019 |
+----+------------+------------+

目标是按如下方式接收我的数据

The goal is to receive my data as follows

+----+------------+
| id |    date    |
+----+------------+
|  1 | 09-20-2019 |
|  1 | 09-21-2019 |
|  1 | 09-22-2019 |
|  1 | 09-23-2019 |
|  1 | 09-24-2019 |
|  1 | 09-25-2019 |
+----+------------+

我正在跟踪此处显示的示例: https://stackoverflow.com/a/54538866/1731057 但是由于某种原因，我的递归函数正在寻找'cte'表.

I'm following an example seen here: https://stackoverflow.com/a/54538866/1731057 But for some reason my recursive function is looking for the 'cte' table.

查询1错误:表'project.cte'不存在

Query 1 ERROR: Table 'project.cte' doesn't exist

WITH cte AS (
    SELECT date_from
    FROM event_dates
  UNION ALL
    SELECT DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) 
    FROM cte
    WHERE DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) <= event_dates.date_until
)
select * FROM cte;

推荐答案

递归CTE的结构已关闭，并且联合的上半部分应该是种子基础案例.然后，递归部分应将前一天的输入值增加一天:

The structure of your recursive CTE is off, and the upper half of the union should be a seed base case. Then, the recursive part should add one day to the previous incoming value:

WITH RECURSIVE cte (n, dt) AS (
    SELECT 1, '2019-09-20'
    UNION ALL
    SELECT n + 1, TIMESTAMPADD(DAY, n, '2019-09-20') FROM cte WHERE n <= 5
)

SELECT * FROM cte;

演示

值得注意的是，我们在这里使用TIMESTAMPADD()来解决INTERVAL表达式的问题，该表达式不能真正使用变量.

Of note, we use TIMESTAMPADD() here to get around the problem of the INTERVAL expression, which can't really take a variable.

如果要使用此方法来生成一系列与表中的from和to值匹配的日期，则可以尝试联接:

If you want to use this approach to generate a series of dates which matches the from and to values in your table, then you can try a join:

SELECT
    t1.dt
FROM cte t1
INNER JOIN yourTable t2
    ON t1.dt BETWEEN t2.from_date AND t2.to_date;

以这种方式使用时，递归CTE充当日历表.

When used this way, the recursive CTE is acting as a calendar table.

这篇关于MySQL递归CTE表不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！