对MySQL 4(非5)中的逗号分隔列求和 [英] Summing a comma separated column in MySQL 4 (not 5)

问题描述

我正在编写一个查询，该查询将一个表中的数据选择到另一个表中，需要移动的列之一是DECIMAL列.由于我无法控制的原因，源"列有时可能是用逗号分隔的数字列表.有没有一种优雅的sql唯一方法可以做到这一点?

例如:

源列

10.2
5,2.1
4

应生成目标列

10.2
7.1
4

我正在使用MySQL 4，顺便说一句.

解决方案

要执行这种非平凡的字符串操作，您需要使用存储过程，对于MySQL而言，存储过程仅在6年前的5.0版中出现. /p>

MySQL 4现在已经很旧了，分支4.1的最新版本是4.1.25，在2008年.它不再受支持.大多数Linux发行版不再提供它.确实是时候升级了.

以下是适用于MySQL 5.0+的解决方案:

DELIMITER //
CREATE FUNCTION SUM_OF_LIST(s TEXT)
  RETURNS DOUBLE
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE res DOUBLE DEFAULT 0;
  WHILE INSTR(s, ",") > 0 DO
    SET res = res + SUBSTRING_INDEX(s, ",", 1);
    SET s = MID(s, INSTR(s, ",") + 1);
  END WHILE;
  RETURN res + s;
END //
DELIMITER ;

示例:

mysql> SELECT SUM_OF_LIST("5,2.1") AS Result;
+--------+
| Result |
+--------+
|    7.1 |
+--------+

I'm writing a query that selects data from one table into another, one of the columns that needs to be moved is a DECIMAL column. For reasons beyond my control, the source column can sometimes be a comma separated list of numbers. Is there an elegant sql only way to do this?

For example:

source column

10.2
5,2.1
4

Should produce a destination column

10.2
7.1
4

I'm using MySQL 4, btw.

解决方案

To do this kind of non trivial string manipulations, you need to use stored procedures, which, for MySQL, only appeared 6 years ago, in version 5.0.

MySQL 4 is now very old, the latest version from branch 4.1 was 4.1.25, in 2008. It is not supported anymore. Most Linux distributions don't provide it anymore. It's really time to upgrade.

Here is a solution that works for MySQL 5.0+:

DELIMITER //
CREATE FUNCTION SUM_OF_LIST(s TEXT)
  RETURNS DOUBLE
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE res DOUBLE DEFAULT 0;
  WHILE INSTR(s, ",") > 0 DO
    SET res = res + SUBSTRING_INDEX(s, ",", 1);
    SET s = MID(s, INSTR(s, ",") + 1);
  END WHILE;
  RETURN res + s;
END //
DELIMITER ;

Example:

mysql> SELECT SUM_OF_LIST("5,2.1") AS Result;
+--------+
| Result |
+--------+
|    7.1 |
+--------+

这篇关于对MySQL 4(非5)中的逗号分隔列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！