变量为(select…)sql

f4t66c6m 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(320)

这是一个非常愚蠢的问题，但我不断抨击自己的墙，我甚至不知道究竟什么是谷歌。
我有一个数据库，我正在做一些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。

sql mysql

来源：https://stackoverflow.com/questions/53913003/variable-as-select-sql

1条答案

按热度按时间

izkcnapc1#

假设您运行的是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;

赞(0）回复(0）举报 2021-06-16

我来回答

变量为(select…)sql

1条答案

相关问题

热门标签

最新问答