MySQL 在运行时从未知列获取总查询 [英] MySQL Get Total Query from Unknown Columns at Runtime

问题描述

我试图在运行时从表中获取未知列的总数.

I am trying to get the total of the unknown columns at runtime from a table.

这是source_table:

Here's the source_table:

+----+------------+-----------+-----------+-----------+-----------+-----------+
| ID | Name       | Unknown 1 | Unknown 2 | Unknown 3 | Unknown 4 | Unknown 5 |
+----+------------+-----------+-----------+-----------+-----------+-----------+
| 1  | abc        |     10.00 |     18.00 |      5.00 |     21.00 |      6.00 |
+----+------------+-----------+-----------+-----------+-----------+-----------+
| 2  | ghq        |     22.00 |     14.00 |     12.00 |     11.00 |     23.00 |
+----+------------+-----------+-----------+-----------+-----------+-----------+
| 3  | xyz        |     35.00 |      8.00 |     16.00 |      7.00 |      4.00 |
+----+------------+-----------+-----------+-----------+-----------+-----------+

这是我想要实现的结果表:

And here's the desired result_table I am trying to achieve:

+-----------+-----------+
| MyColumns |   Total   |
+-----------+-----------+
| Unknown 1 |     67.00 |
+-----------+-----------+
| Unknown 2 |     40.00 |
+-----------+-----------+
| Unknown 3 |     33.00 |
+-----------+-----------+
| Unknown 4 |     39.00 |
+-----------+-----------+
| Unknown 5 |     33.00 |
+-----------+-----------+

我已经弄清楚了上面未知列(第一个字段)的查询.但是，我在如何使用下面的尝试代码获取总列(第二个字段)时遇到了困难:

I already figured out the query for the unknown column (1st field) above. But, I am having difficulty how to get the total column (2nd field) with my attempt code below:

SELECT `COLUMN_NAME`
FROM  `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = 'mydb' 
AND   `TABLE_NAME`   = 'source_table'
AND   `COLUMN_NAME` NOT LIKE 'ID'
AND   `COLUMN_NAME` NOT LIKE 'Name';

我将不胜感激这里使用我上面的代码获得总列(第二个字段)的任何帮助.

I will appreciate any help here to get me the total column (2nd field) using my code above.

更新 1:@Akina 回答 result_table — 引用此处更正''',COLUMN_NAME, '''.

UPDATE 1: @Akina answer result_table — CORRECTED BY QUOTING HERE ''',COLUMN_NAME, '''.

+-----------+-----------+
| MyColumns |   Total   |
+-----------+-----------+
|     10.00 |     67.00 |
+-----------+-----------+
|     18.00 |     40.00 |
+-----------+-----------+
|      5.00 |     33.00 |
+-----------+-----------+
|     21.00 |     39.00 |
+-----------+-----------+
|      6.00 |     33.00 |
+-----------+-----------+

推荐答案

SELECT GROUP_CONCAT( CONCAT( 'SELECT ''',
                             COLUMN_NAME,
                             ''' Columns, SUM(`',
                             COLUMN_NAME,
                             '`) Total FROM mydb.source_table' )
                     SEPARATOR '\nUNION ALL\n' )
INTO @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' 
  AND TABLE_NAME   = 'source_table'
  AND COLUMN_NAME NOT IN ('ID', 'Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

小提琴

出于调试目的 - 首先执行删除 INTO 子句的 SELECT 查询(您也可以编辑为 SEPARATOR '\nUNION ALL\n' - 查询文本将更清晰以供查看)，检查构建的查询文本在综合上是正确的，然后执行它并检查其输出是否也正确.如果不是 - 编辑.

For debugginig purposes - execute only SELECT query removing INTO clause firstly (also you may edit to SEPARATOR '\nUNION ALL\n' - the query text will be more clear for to view), check that built query text is correct synthactically, then execute it and check that its output is correct too. If not - edit.

可能的问题来源 - 列名可能是保留字或可能包含空格或其他有问题的字符.在这种情况下，您必须在 CONCAT 中为包装字符串文字添加反引号.

Possible problem source - the column name may be a reserved word or may concain spaces or another problematic chars. In such case you must add quoting backticks to wrapping string literals in CONCAT.

附注.代码已编辑，所有提到的版本(和数据库名称 - 用于使代码独立于 CurrentDB)添加到.

PS. Code edited, all mentioned editions (and database name - for to make the code CurrentDB-independent) added into.

PPS.如果需要，可以将数据库和表名(甚至跳过的名称列表)转换为参数，使代码更通用...

PPS. Database and table names (and even skipped names list) may be converted to parameters if needed, making the code more universal...

这篇关于MySQL 在运行时从未知列获取总查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！