56,679
社区成员
发帖
与我相关
我的任务
分享
declare Ttype varchar(30);
declare Tid int;
declare Tmin int;
declare Tmax int;
select min(A1) into Tmin from A;
select max(A1) into Tmax from A;
while Tmin<=Tmax do
select A3 into Ttype from A where A1=Tmin;-- C表C2字段 类型名
select A1 into Tid from A where A3=Tmin;-- C表C1字段 类型定义ID(来源A表)
set @Tsql=concat("insert into C(C1,C2) select ",Tid,",",Ttype," from B;");
prepare Ttidy from @Tsql;
execute Tidy;
-- C表插入数据;
Tmin=Tmin+1;
end while;
mysql> select * from config;
+----+----------+
| id | name |
+----+----------+
| 1 | salary |
| 2 | bonus |
| 3 | overtime |
+----+----------+
3 rows in set (0.00 sec)
mysql> select * from data;
+--------+-------+----------+
| salary | bonus | overtime |
+--------+-------+----------+
| 1 | 2 | 4 |
| 3 | 3 | 3 |
+--------+-------+----------+
2 rows in set (0.00 sec)
mysql> select @sql := concat("select ", group_concat(name), " from data;")
-> from config
-> where id in (1, 2);
+--------------------------------------------------------------+
| @sql := concat("select ", group_concat(name), " from data;") |
+--------------------------------------------------------------+
| select salary,bonus from data; |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> prepare sp from @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute sp;
+--------+-------+
| salary | bonus |
+--------+-------+
| 1 | 2 |
| 3 | 3 |
+--------+-------+
2 rows in set (0.00 sec)
mysql> drop prepare sp;
Query OK, 0 rows affected (0.00 sec)