将函数应用于表的所有列
将函数应用于表的所有列
提问于 2019-12-19 05:30:03
我正在尝试用MySQL对表中的所有列进行计算。
表: bev
Jahr GKZ gesamt A B C
2017 1111000 88.519 855 888 814
2017 1112000 247.943 2.414 2.379 2.262
2017 1113000 253.106 2.290 2.343 2.289
2017 1113004 43.392 408 416 403
2017 1113008 12.383 137 134 124
2017 1113012 27.106 252 252 249
2017 1113016 41.673 391 410 398
2017 1113020 39.585 364 391 373
2017 1113024 10.075 63 73 74
2017 1113028 24.083 199 205 209
2017 1113032 8.745 63 77 65
2017 1113036 18.143 170 170 143
2017 1113040 27.921 243 215 251表: ja
GKZ Jahr ja_name
1001000 2017 K X
1002000 2017 K Y
5370000 2017 L Z
5370004 2017 Z1
5370012 2017 Z2
5370016 2017 Z3
5370020 2017 Z4我已经在函数中得到了一列(第一列: gesamt)的计算结果：
CREATE DEFINER=`DB`@`%` FUNCTION `Total_Amount_Funct`(
bev_ID int(11),
bev_Total int(11),
ja_name VARCHAR(255),
ja_jahr int(11)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE Total_Amount int(11);
DECLARE kreis int(11);
DECLARE Total_Sum int(11);
SET kreis = (bev_ID / 1000) ;
SET Total_Sum = (SELECT SUM(b.gesamt)
FROM bev as b, ja as j
WHERE b.GKZ = j.GKZ
AND b.Jahr = j.Jahr
AND j.Jahr = ja_jahr
AND (MOD(b.GKZ, 1000) <> 0)
AND (MOD(b.GKZ, 1000) != 0)
AND NOT (MOD(b.GKZ, 1000) = 0)
AND (b.GKZ BETWEEN (kreis*1000 + 1) AND (((kreis+1)*1000)-1))
AND j.ja_name IS NOT NULL);
SET Total_Amount = bev_Total-Total_Sum;
RETURN (Total_Amount);
END 可以使用以下select命令调用此函数：
SELECT DISTINCT
bev.GKZ,
bev.Jahr,
bev.gesamt,
CASE WHEN (bev.GKZ % 1000 = 0) THEN
coalesce(Total_Amount_Funct(bev.GKZ, bev.gesamt, ja.ja_name, bev.Jahr), bev.gesamt)
ELSE bev.gesamt
END AS bev,
ja.ja_name
FROM
ja, bev
WHERE
bev.GKZ = ja.GKZ
AND bev.Jahr = ja.Jahr; 我真的希望将该函数应用于表的所有列。也许可以作为一个存储过程？也许可以作为动态列。我不知道。我已经在MS SQL中用动态列解决了这个问题，但我感觉翻译它比尝试将函数作为存储过程完成要花费更多的时间。
列的名称可以通过以下方式获取：
SELECT column_name
FROM information_schema.columns
WHERE table_name='bev'
and column_name not in ('Jahr','GKZ'); 因此它应该是：
GKZ Jahr gesamt bev ja_name
1111000 2017 88.519 88.519 K X
1112000 2017 247.943 247.943 K Y
1113000 2017 253.106 101.350 L Z
1113004 2017 43.392 43.392 Z1
1113012 2017 27.106 27.106 Z2
1113016 2017 41.673 41.673 Z3
1113020 2017 39.585 39.585 Z4回答 2
Stack Overflow用户
发布于 2019-12-19 06:42:16
由于您仅在SUM中使用列，因此可以将列名作为参数传递，并使用CASE-statement来选择相应的列。类似于：
CREATE FUNCTION `Total_Amount_Funct`(
bev_ID decimal(8,3),
bev_Total int,
ja_name VARCHAR(255),
ja_jahr int,
in_col varchar(10)
)
RETURNS int
DETERMINISTIC
BEGIN
DECLARE Total_Amount int(11);
DECLARE Total_Sum int(11);
SELECT
SUM(
case
when in_col='gesamt' then b.gesamt
when in_col='A' then b.A
when in_col='B' then b.B
when in_col='C' then b.C
end
) into Total_Sum
FROM bev as b
join ja as j on b.GKZ = j.GKZ AND b.Jahr = j.Jahr
WHERE
MOD(b.GKZ, 1000) != 0
AND b.GKZ BETWEEN bev_ID+1 AND bev_ID+999
AND j.ja_name IS NOT NULL
SET Total_Amount = bev_Total-Total_Sum;
RETURN (Total_Amount);
END 然后使用列名和正确的值调用函数：
Total_Amount_Funct(bev.GKZ, bev.gesamt, ja.ja_name, bev.Jahr, 'gesamt'),
Total_Amount_Funct(bev.GKZ, bev.A, ja.ja_name, bev.Jahr, 'A')
...注意，调用一个进行查询的函数将序列化您的SQL (在每一行上调用该函数会导致在每一行上执行该函数查询)。这将损害查询性能。
Stack Overflow用户
发布于 2019-12-24 06:18:03
slaakso，
非常感谢你的回答。从今天起你就是我的偶像:-)。
谢谢，谢谢。
我可能有一个性能问题。
可以为表bev的所有列编写函数。我们可以复制临时表中的列名：
CREATE TEMPORARY TABLE listColumns(
Columns_ID MEDIUMINT NOT NULL AUTO_INCREMENT ,
Columnsnamen varchar(256) ,
PRIMARY KEY (Columns_ID)
);已从系统信息中读取：
insert into listColumns (Columnsnamen)
SELECT column_name
FROM information_schema.columns
WHERE table_name='bev'
and column_name not in ('Jahr','GKZ');此表如下所示：
Columns_ID Columnsnamen
1 gesamt
2 A
3 B
4 C
5
6 以这种方式，没有必要提及每个列名(表包含大约100列)。也许把光标放在Columns_ID上？
如果你能给我另一个建议，那就太好了。
谢谢你和善良的瑞吉斯
分析
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59400246
复制相关文章
相似问题
腾讯云开发者
