对 MySQL 4（不是 5）中的逗号分隔列求和

发布于 2024-11-15 04:40:59 字数 249 浏览 3 评论 0原文

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

例如：

源列

10.2
5,2.1
4

应该生成目标列

10.2
7.1
4

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

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黎歌 2024-11-22 04:40:59

要进行这种重要的字符串操作，您需要使用存储过程，对于 MySQL 来说，存储过程仅在 6 年前的 5.0 版本中出现。

MySQL 4 现在已经很老了，分支 4.1 的最新版本是 2008 年的 4.1.25。不再支持它。大多数 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 |
+--------+

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 |
+--------+

回复收藏 0 原文

沫尐诺 2024-11-22 04:40:59

这是一个用于分割字符串的 mysql 函数：

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

你必须这样使用它：

SELECT SPLIT_STR(FIELD, ',', 1) + SPLIT_STR(FIELD, ',', 2)  FROM TABLE

Here is a mysql function to split a string:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And u have to use it this way:

SELECT SPLIT_STR(FIELD, ',', 1) + SPLIT_STR(FIELD, ',', 2)  FROM TABLE

回复收藏 0 原文

清浅ˋ旧时光 2024-11-22 04:40:59

不幸的是，mysql 不包含字符串分割函数或聚合，因此您需要在存储过程中或在客户端执行此操作。

回复收藏 0 原文

情深如许 2024-11-22 04:40:59

基于数字表的解析方法可以在此 SQLFiddle 链接找到。本质上，一旦有了子字符串，sum 函数就会自动转换数字。为了方便起见：

create table scores (id int primary key auto_increment, valueset varchar(30));
insert into scores (valueset) values ('7,6,8');
insert into scores (valueset) values ('3,2');

create table numbers (n int primary key auto_increment, stuffer varchar(3));
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);

SELECT ID, SUM(SCORE) AS SCORE
FROM (
        SELECT
          S.id
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(S.valueset, ',', numbers.n),',',-1) score
          , Numbers.n
        FROM
          numbers
          JOIN scores S ON CHAR_LENGTH(S.valueset)
            -CHAR_LENGTH(REPLACE(S.valueset, ',', ''))>=numbers.n-1
) Z
GROUP BY ID
  ;

A number table-based parse approach can be found at this SQLFiddle link. Esentially, once you have the substrings, the sum function will auto-cast the numbers. For convenience:

create table scores (id int primary key auto_increment, valueset varchar(30));
insert into scores (valueset) values ('7,6,8');
insert into scores (valueset) values ('3,2');

create table numbers (n int primary key auto_increment, stuffer varchar(3));
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);

SELECT ID, SUM(SCORE) AS SCORE
FROM (
        SELECT
          S.id
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(S.valueset, ',', numbers.n),',',-1) score
          , Numbers.n
        FROM
          numbers
          JOIN scores S ON CHAR_LENGTH(S.valueset)
            -CHAR_LENGTH(REPLACE(S.valueset, ',', ''))>=numbers.n-1
) Z
GROUP BY ID
  ;

回复收藏 0 原文

~没有更多了~