mysql - 变量 AS (SELECT...) SQL

Question

这是一个非常愚蠢的问题，但我不断地把自己从墙上敲下来，我什至不知道到底要谷歌什么。

我有一个数据库，并且正在执行一些 SQL 查询。这个工作正常。

SELECT 
year(datetime_buy), 
month(datetime_buy), 
count(distinct email) AS number
FROM ticket
WHERE year(datetime_buy)=2018
AND email IN (
    SELECT DISTINCT email 
    FROM ticket 
    WHERE Month(datetime_buy)=1
    AND Year(datetime_buy)=2018 AND datetime_cancel IS NULL)
GROUP BY 1, 2;

但是当我尝试为将来保存一些SELECT(例如jan_tickets)时:

jan_tickets AS (
SELECT DISTINCT email 
FROM ticket 
WHERE Month(datetime_buy)=1
AND Year(datetime_buy)=2018
AND datetime_cancel IS NULL),

SELECT
year(datetime_buy),
month(datetime_buy),
count(distinct email) AS number
FROM ticket
WHERE year(datetime_buy)=2018
AND email IN jan_tickets
GROUP BY 1, 2;

我收到错误:

Error SQL (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 'jan_tickets AS (SELECT DISTINCT email FROM ticket
WHERE Month(datetime_buy)=1 AN' at line 1

更新:我使用 MySQL 5.6。

Answer 1

假设您运行的是 MySQL 8+，那么对您的以下更新将起作用:

WITH jan_tickets AS (
    SELECT DISTINCT email
    FROM ticket
    WHERE DATE_FORMAT(datetime_buy, '%Y-%m') = '2018-01' AND datetime_cancel IS NULL
)

SELECT
    YEAR(datetime_buy),
    MONTH(datetime_buy),
    COUNT(DISTINCT email) AS number
FROM ticket
WHERE
    YEAR(datetime_buy) = 2018 AND
    email IN (SELECT email FROM jan_tickets)
GROUP BY 1, 2;

此处的注释包括 CTE 定义必须以 WITH 开头。后面的子序列 CTE 不使用 WITH，但应以逗号分隔。最后一个 CTE 定义之后、使用它的查询之前没有逗号。在您的情况下，您需要在 WHERE 子句中使用子查询来从 CTE 选择电子邮件。

编辑:

由于您仅使用 MySQL 5.6，因此无法使用公用表表达式。因此，我们可以内联 CTE 中包含的子查询:

SELECT
    YEAR(datetime_buy),
    MONTH(datetime_buy),
    COUNT(DISTINCT email) AS number
FROM ticket
WHERE
    YEAR(datetime_buy) = 2018 AND
    email IN (
        SELECT DISTINCT email
        FROM ticket
        WHERE DATE_FORMAT(datetime_buy, '%Y-%m') = '2018-01' AND
              datetime_cancel IS NULL
    )
GROUP BY 1, 2;