mysql - 选择和插入查询的 MySQL 变量语法

Question

我想仅使用选定的未知列重新生成 Source_Table 并在最后一行插入总计。

Source_Table
+----+---------+-----------+-----------+-----------+-----------+-----------+
| id | name    | unknown_1 | unknown_2 | unknown_3 | unknown_4 | unknown_5 | 
+----+---------+-----------+-----------+-----------+-----------+-----------+
| 1  | ABC     | 12        | 22        | 13        | 14        | 5         | 
+----+---------+-----------+-----------+-----------+-----------+-----------+
| 2  | GHQ     | 16        | 7         | 18        | 9         | 10        | 
+----+---------+-----------+-----------+-----------+-----------+-----------+
| 3  | XYZ     | 8         | 14        | 2         | 15        | 20        | 
+----+---------+-----------+-----------+-----------+-----------+-----------+

我能够使用动态查询生成一个表，以按 ASC 顺序获取总数，如下所示：

Total_Table
+------------+---------+
| supplier   | total   |
+------------+---------+
| unknown_3  | 33      |
+------------+---------+
| unknown_5  | 35      |
+------------+---------+
| unknown_1  | 36      |
+------------+---------+

现在，我想查询以下Source_Table列出的供应商Total_Table：

Query_Result
+---------+-----------+-----------+-----------+
| name    | unknown_3 | unknown_5 | unknown_1 | 
+---------+-----------+-----------+-----------+
| ABC     | 13        | 5         | 12        |
+---------+-----------+-----------+-----------+
| GHQ     | 18        | 10        | 16        |
+---------+-----------+-----------+-----------+
| XYZ     | 2         | 20        | 8         | 
+---------+-----------+-----------+-----------+
| Total   | 33        | 35        | 36        | 
+---------+-----------+-----------+-----------+

我尝试了以下代码，但我对变量的语法有困难：

SET @Supplier1 = (SELECT supplier FROM Total_Table LIMIT 0, 1);
SET @Supplier2 = (SELECT supplier FROM Total_Table LIMIT 1, 1);
SET @Supplier3 = (SELECT supplier FROM Total_Table LIMIT 2, 1);

INSERT INTO Source_Table (name, @Supplier1, @Supplier2, @Supplier3)
VALUES ('Total',
       (SELECT total FROM Total_Table LIMIT 0, 1),
       (SELECT total FROM Total_Table LIMIT 1, 1),
       (SELECT total FROM Total_Table LIMIT 2, 1));  

SELECT name, @Supplier1, @Supplier2, @Supplier3 FROM Source_Table;

我尝试用单/双引号和反引号括起来，但无济于事。

在这些类型的代码中，变量的正确语法应该是什么？

标签： mysqlsqlvariablesdynamic-sqlmysql-8.0