MySQL使用CTE时SET命令的正确放置位置是什么？

你当前的SET命令位置是错误的。

MySQL语法规定，WITH定义的公共表表达式（CTE）必须直接紧跟在它所服务的SELECT、INSERT这类执行语句之前，中间不能插入SET这类独立的变量赋值语句，否则会直接触发语法错误。

正确的摆放位置是把变量初始化的SET语句放在整个逻辑的最前面，先完成变量赋值再定义CTE执行查询，修正后的代码如下：

-- 先执行变量初始化
SET @r1=0, @r2=0, @r3=0, @r4=0;

-- CTE定义直接跟在后续的SELECT语句前
WITH cte AS(
    SELECT emp.emp_no, dep.dept_name 
    FROM dept_emp emp
    JOIN departments dep
    ON emp.dept_no=dep.dept_no
)
SELECT
    CASE
        WHEN dept_name ="Finance" THEN (@r1:=@r1+1)
        WHEN dept_name ="Development" THEN (@r2:=@r2+1)
        WHEN dept_name ="Marketing" THEN (@r3:=@r3+1)
        WHEN dept_name ="Production" THEN (@r4:=@r4+1) 
    END AS rownums,
    CASE WHEN dept_name ="Finance" THEN emp_no END AS "Finance",
    CASE WHEN dept_name ="Development" THEN emp_no END AS "Development",
    CASE WHEN dept_name ="Marketing" THEN emp_no END AS "Marketing",
    CASE WHEN dept_name ="Production" THEN emp_no END AS "Production"
FROM cte;

如果你使用的是MySQL 8.0及以上版本，也可以直接用窗口函数ROW_NUMBER()实现按部门分组自增序号，不需要自定义用户变量，写法更简洁稳定：

WITH cte AS(
    SELECT emp.emp_no, dep.dept_name 
    FROM dept_emp emp
    JOIN departments dep
    ON emp.dept_no=dep.dept_no
)
SELECT 
    ROW_NUMBER() OVER(PARTITION BY dept_name ORDER BY emp_no) AS rownums,
    CASE WHEN dept_name ="Finance" THEN emp_no END AS "Finance",
    CASE WHEN dept_name ="Development" THEN emp_no END AS "Development",
    CASE WHEN dept_name ="Marketing" THEN emp_no END AS "Marketing",
    CASE WHEN dept_name ="Production" THEN emp_no END AS "Production"
FROM cte;

内容的提问来源于stack exchange，提问作者dgrszombie315