mysql - 显示语法错误，错误代码:1064 接近WITH 'cohort_items AS ( SELECT DATE_TRUNC(' MONTH', U.created_at)::date 作为队列FROM

Question

回应:

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 'cohort_items as ( select month(U.created_at) as cohort' at line 1

这是我的代码，显示错误:

-- - users (id, created_at)
WITH cohort_items AS (
SELECT month(U.created_at) AS cohort_month,
       id AS user_id
FROM public.users U ORDER BY 1,2)

我尝试查找语法错误，但没有成功。请帮我找出我哪里做错了。

Answer 1

Date_trunc 不是 mysql 函数，您可以使用日期或 DATE_FORMAT()

你可以使用这个-

SELECT date(U.created_at) AS cohort_month,
               id AS user_id
       FROM public.users U
       ORDER BY 1,2

或者

SELECT DATE_FORMAT(U.created_at,'%d-%b-%Y') AS cohort_month,
                   id AS user_id
           FROM public.users U
           ORDER BY 1,2