insert语句的询问！

Question

set @A:=22;
insert into 表a (字段a1,字段a2) select 字段b1,@A:=@A+1 from 表b;语句效果是字段a2会,23,24,25,26......无限加下去
想要的效果是:23,24,25,23,24,25......无限加下去
该如何修改？高手指教

Answer 1

mysql> set @a:=1;
Query OK, 0 rows affected (0.00 sec)mysql> set @b:=1;
Query OK, 0 rows affected (0.00 sec)mysql> select *,23+substring(@b:=@a%3,1,1),@a:=@a+1 from test1;
+------+----------------------------+----------+
| a    | 22+substring(@b:=@a%3,1,1) | @a:=@a+1 |
+------+----------------------------+----------+
|    1 |                         23 |        2 |
|    1 |                         24 |        3 |
|    1 |                         22 |        4 |
|    1 |                         23 |        5 |
|    1 |                         24 |        6 |
|    1 |                         22 |        7 |
|    1 |                         23 |        8 |
|    1 |                         24 |        9 |
|    1 |                         22 |       10 |
|    1 |                         23 |       11 |
|    1 |                         24 |       12 |
|    1 |                         22 |       13 |
|    1 |                         23 |       14 |
|    1 |                         24 |       15 |
|    1 |                         22 |       16 |
|    1 |                         23 |       17 |
|    1 |                         24 |       18 |
|    1 |                         22 |       19 |
|    1 |                         23 |       20 |
|    1 |                         24 |       21 |
|    1 |                         22 |       22 |
|    1 |                         23 |       23 |
+------+----------------------------+----------+
22 rows in set (0.00 sec)

Answer 2

set @A:=22;
insert into 表a (字段a1,字段a2) select 字段b1,@A:=if(@A=25,23,@A+1) from 表b;

Answer 3

set @A:=23;
insert into a11 (id,title) select 1,if(@A>25,@A:=24,@A:=@A+1) from a2;

Answer 4

set @A:=22;
insert into 表a (字段a1,字段a2) select 字段b1,if(@A>25,@A=23,@A:=@A+1) from 表b;这样就可以了。