mysql - 计算和限制每个字段值的行数不起作用

我试图限制给定查询的每个字段值的行数。我找到了这个已回答的问题：

here

与该链接的第一个答案一样，我创建了下表：

create table mytab (
    id int not null auto_increment primary key,
    first_column int,
    second_column int
) engine = myisam;

插入此数据：

insert into mytab (first_column,second_column) values
    (1,1),
    (1,4),
    (2,10),
    (3,4),
    (1,4),
    (2,5),
    (1,6);

最后运行此查询

select
    id,
    first_column,
    second_column,
    row_num
from
    (select
        *,
        @num := if(@first_column = first_column, @num + 1, 1) as row_num,
        @first_column:=first_column as c
    from mytab
    order by first_column,id) as t,
    (select @first_column:='',@num:=0) as r;

但是除了得到这个结果之外，每当重复first_column时row_num都会增加，

+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
|  1 |            1 |             1 |       1 |
|  2 |            1 |             4 |       2 |
|  5 |            1 |             4 |       3 |
|  7 |            1 |             6 |       4 |
|  3 |            2 |            10 |       1 |
|  6 |            2 |             5 |       2 |
|  4 |            3 |             4 |       1 |
+----+--------------+---------------+---------+

我得到这个结果：

+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
|  1 |            1 |             1 |       1 |
|  2 |            1 |             4 |       1 |
|  5 |            1 |             4 |       1 |
|  7 |            1 |             6 |       1 |
|  3 |            2 |            10 |       1 |
|  6 |            2 |             5 |       1 |
|  4 |            3 |             4 |       1 |
+----+--------------+---------------+---------+

我从链接上照搬了代码。我检查了SQL Fiddle，代码工作正常。我正在使用XAMPP。那可能是原因吗？如果是这样，是否有任何解决方法可以使上述工作正常进行？

非常感谢您的帮助。提前致谢。

最佳答案

变量分配必须在子查询中。

select
    id,
    first_column,
    second_column,
    row_num
from
    (select
        m.*,
        @num := if(@first_column = first_column, @num + 1, 1) as row_num,
        @first_column:=first_column as c
    from mytab m
    cross join (select @first_column:='',@num:=0) r --this was in the outer query previously
    order by first_column,id
    ) t

关于mysql - 计算和限制每个字段值的行数不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44292896/