MySQL Cte 语句

Question

我正在尝试在 MySQL 中运行 CTE 语句，但在包含 WITH 的行上收到错误。这是我的完整语法，我需要更改什么才能使该查询按照我的意愿执行。

这是我收到的错误:

Error Code: 1064. 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 'VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number) ), cte_tally10' at line 7

SET @v_start_date = '20190101';
SET @v_number_of_months = 12;

WITH
cte_input_values AS (
    SELECT CAST(TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, @v_start_date), 0) AS date) AS start_date
),
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS month_number
    FROM cte_tally10 c1
    CROSS JOIN cte_tally10 c2
),
cte_months_to_pull AS (
    SELECT TIMESTAMPADD(MONTH, t.month_number, start_date) AS month_to_pull
    FROM cte_tally100 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @v_number_of_months - 1
    UNION ALL
    SELECT TIMESTAMPADD(MONTH, t.month_number, TIMESTAMPADD(YEAR, -1, start_date)) AS month_to_pull
    FROM cte_tally100 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @v_number_of_months - 1
)
SELECT 
    INSERT(DATE_FORMAT (month_to_pull, 7), 4, 4, '') AS month_year,
    IFNULL(YRS.SaleAmount, 0) AS total_sales
FROM cte_months_to_pull
LEFT OUTER JOIN (
    SELECT TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM CurrentYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= start_date AND 
        SaleDate < TIMESTAMPADD(MONTH, @v_number_of_months, start_date)
    GROUP BY TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0)
    UNION ALL
    SELECT TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM PrevYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= TIMESTAMPADD(YEAR, -1, start_date) AND
        SaleDate < TIMESTAMPADD(MONTH, @v_number_of_months, TIMESTAMPADD(YEAR, -1, start_date))
    GROUP BY TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0)
) AS YRS ON SaleMonth = month_to_pull
ORDER BY MONTH(month_to_pull), YEAR(month_to_pull)

Answer 1

我可能弄错了，但我认为 MySQL 不支持这种形式的 VALUES；通常你会做类似的事情

SELECT * FROM (SELECT 0 AS 编号 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) AS编号