mysql - 如何获取MySQL子查询GROUP BY结果的行号？

Question

我对如何使用此 MySQL 查询作为子查询来获取 GROUP BY 结果的连续编号感到困惑。 GROUP BY 仍然具有 GROUP BY 执行之前查询的行号，因此 row_count 为 14 和 1，如下图所示。

SET @row_count:=0;
SELECT @row_count:=@row_count+1 AS row_count,column_id, type
FROM mt_report_filters
WHERE report_id = 2
GROUP BY column_id

我试图将其构建到子查询中，以使用此逻辑获取连续的 row_count 1 和 2，但这是不正确的。

SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number FROM t1, column_id, type
FROM(
    SELECT column_id, type
    FROM mt_report_filters
    WHERE report_id = 2
    GROUP BY column_id
    AS t1)

Answer 1

phpMyAdmin 给我错误消息:#1248 - 每个派生表必须有自己的别名。从那里我想到我需要为从子查询创建的表添加别名，所以我只是在末尾添加了“AS t1”。

SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number, column_id, type 
FROM (
    SELECT column_id, type 
    FROM mt_report_filters
    WHERE report_id = 2
    GROUP BY column_id) AS t1