begin
declare i int;
set i=0;
insert into 表a(id,name) (select i,Name from 表b);end现在问题来了,如果select name from 表b有50条记录的话,怎么能让i的值每次都递增1呢?想了好半天了,等待高手!
declare i int;
set i=0;
insert into 表a(id,name) (select i,Name from 表b);end现在问题来了,如果select name from 表b有50条记录的话,怎么能让i的值每次都递增1呢?想了好半天了,等待高手!
insert into @tb(name) select '张三'
union all select '李四'
select * from @tbid name
----------- ----------
1 张三
2 李四(2 行受影响)
插入一张变量表和一个变量字段效率估计还是差别挺大的.....况且还是mssql的,不过非常谢谢你啊
Query OK, 0 rows affected (0.13 sec)mysql> create table b(id int,name char(10))
Query OK, 0 rows affected (0.06 sec)mysql> insert into b values
-> (1 ,'A01'),
-> (2 ,'A02'),
-> (3 ,'A03'),
-> (4 ,'A04'),
-> (5 ,'A05'),
-> (6 ,'A06'),
-> (7 ,'A07'),
-> (8 ,'A08'),
-> (9 ,'A09'),
-> (10 ,'A10');
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0mysql> select * from b;
+------+------+
| id | name |
+------+------+
| 1 | A01 |
| 2 | A02 |
| 3 | A03 |
| 4 | A04 |
| 5 | A05 |
| 6 | A06 |
| 7 | A07 |
| 8 | A08 |
| 9 | A09 |
| 10 | A10 |
+------+------+
10 rows in set (0.00 sec)mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE simpleproc ()
-> BEGIN
-> declare i int;
-> set @i=0;
-> insert into a (id,name) select @i:=@i+1 ,name from b;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call simpleproc();
Query OK, 10 rows affected (0.08 sec)mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | A01 |
| 2 | A02 |
| 3 | A03 |
| 4 | A04 |
| 5 | A05 |
| 6 | A06 |
| 7 | A07 |
| 8 | A08 |
| 9 | A09 |
| 10 | A10 |
+------+------+
10 rows in set (0.00 sec)mysql>
select @i:=@i+1 因为是直接给session 变量赋值可以,而直接到local 变量好象不行。@i:=@i+1 就把把 i +1 递增以实现这个序号。