时间戳

首页 > 解决方案 > 如何使用 CTE 和左连接编写查询

mysql - 如何使用 CTE 和左连接编写查询

问题描述

我正在尝试使用我的 CTE 建立一个日历表并进行设置，以便我的查询中的日期像这样显示

1 月 18 日
1 月 19
日 2 月 18日 2
月 19 日

现在这是我的 DDL，这是我尝试的查询，但是在 MySql Workbench 中，我收到错误消息，即我的 sql 某处存在错误。

这是确切的错误：

查询错误：错误：ER_PARSE_ERROR：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的 'RECURSIVE cte_months_to_pull AS ( SELECT DATE_FORMAT(@start_date, '%Y-%m-01'' ) 附近使用正确的语法

有人可以帮忙吗？

如果它更容易，这是一个 SQL Fiddle http://sqlfiddle.com/#!9/300f9d/1

CREATE TABLE PrevYear (


`EmployeeNumber` char(8) NOT NULL,
  `SaleAmount` int DEFAULT NULL,
  `SaleDate` date NOT NULL,
  `EmployeeName` char(17) NOT NULL
); 

CREATE TABLE CurrentYear (
  `EmployeeNumber` char(8) NOT NULL,
  `SaleAmount` int DEFAULT NULL,
  `SaleDate` date NOT NULL,
  `EmployeeName` char(17) NOT NULL
); 

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-01-10', 'Maggie Samuels');
INSERT INTO CurrentYear
VALUES ('ea12', '199', '2019-01-13', 'Sam Stoner');
INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-03-01', 'Jake Jolel');
INSERT INTO CurrentYear
VALUES ('ls22', '100', '2019-05-01', 'Maggie Samuels');

INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-01-10', 'Maggie Samuels');
INSERT INTO PrevYear
VALUES ('ea12', '199', '2018-01-13', 'Sam Stoner');
INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-03-01', 'Sam Stoner');
INSERT INTO PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Maggie Samuels');

这是我尝试的查询：

    SET @start_date = '20190102';
SET @number_of_months = 12;
WITH RECURSIVE
cte_months_to_pull AS (
    SELECT DATE_FORMAT(@start_date, '%Y-%m-01')
         - INTERVAL @number_of_months MONTH AS month_to_pull
    UNION ALL
    SELECT month_to_pull + INTERVAL 1 MONTH
    FROM cte_months_to_pull
    WHERE month_to_pull < @start_date + INTERVAL @number_of_months - 2 MONTH
)
SELECT Date_format(saledate, '%m-%Y') AS Month,
               employeename,
               Sum(saleamount)                AS IA
        FROM   currentyear
        WHERE  employeename = 'Maggie Samuels'
        GROUP  BY Date_format(saledate, '%m-%Y'), employeename
        UNION ALL
        SELECT Date_format(saledate, '%m-%Y') AS Month,
               employeename,
               Sum(saleamount)                AS IA
        FROM   prevyear
        WHERE  employeename = 'Maggie Samuels'
        GROUP  BY Date_format(saledate, '%m-%Y'), employeename
LEFT JOIN cte_months_to_pull (
    Select DATE_Format(month_to_pull, '%b %y')
    FROM cte_months_to_pull
) AS YRS ON month_to_pull = saledate
ORDER BY MONTH(month_to_pull), YEAR(month_to_pull)

标签： mysqlsqlleft-joincommon-table-expression

解决方案

如我所见，您使用的 MySQL 版本早于 8.0，它不支持 RECURSIVE CTE。我已经用 8.0 的一些小更新尝试了您的查询，它运行良好 -

WITH RECURSIVE
cte_months_to_pull AS (
    SELECT DATE_FORMAT(@start_date, '%Y-%m-01')
         - INTERVAL @number_of_months MONTH AS month_to_pull
    UNION ALL
    SELECT month_to_pull + INTERVAL 1 MONTH
    FROM cte_months_to_pull
    WHERE month_to_pull < @start_date + INTERVAL @number_of_months - 2 MONTH
)
SELECT YRS.months_to_pull
      ,T.employeename
      ,COALESCE(T.IA, 0) IA
FROM (SELECT DATE_Format(month_to_pull, '%b-%Y') months_to_pull
      FROM cte_months_to_pull
      ORDER BY months_to_pull
     ) AS YRS
LEFT JOIN (SELECT Date_format(saledate, '%b-%Y') AS `Month`
                 ,employeename
                 ,Sum(saleamount) AS IA
           FROM   CurrentYear
           WHERE  employeename = 'Maggie Samuels'
           GROUP  BY Date_format(saledate, '%b-%Y'), employeename
           UNION ALL
           SELECT Date_format(saledate, '%b-%Y')
                 ,employeename
                 ,Sum(saleamount)
           FROM   PrevYear
           WHERE  employeename = 'Maggie Samuels'
           GROUP  BY Date_format(saledate, '%b-%Y'), employeename) T
ON YRS.months_to_pull = T.`Month`
ORDER BY month(STR_TO_DATE(CONCAT('01-',months_to_pull), '%d-%b-%Y'))
        ,YEAR(STR_TO_DATE(CONCAT('01-',months_to_pull), '%d-%b-%Y'))

这是小提琴

由于没有预期的输出，我只尝试运行查询。

推荐阅读