在 MySQL 4(不是 5)中总结逗号分隔的列

2021-11-20 00:00:00 mysql mysql4

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

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?

例如:

源列

10.2
5,2.1
4

应该产生一个目标列

10.2
7.1
4

顺便说一句，我使用的是 MySQL 4.

I'm using MySQL 4, btw.

推荐答案

要进行这种非平凡的字符串操作，您需要使用存储过程，对于 MySQL，它在 6 年前才出现在 5.0 版本中.

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 现在很老了，分支 4.1 的最新版本是 4.1.25，在 2008 年.不再支持.大多数 Linux 发行版不再提供它.是时候升级了.

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.

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

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 ;

示例:

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

相关文章