时间戳

首页 > 解决方案 > 根据 MySQL 5.6 和 ORACLE 11 和 ORACLE 12 中的现有列填充列

mysql - 根据 MySQL 5.6 和 ORACLE 11 和 ORACLE 12 中的现有列填充列

问题描述

我有一个表，其中一列是 varchar 类型，其中包含“1.2.1”、“1”、“0.2”等值。

通常最多有 3 个数字，用小数 (.)

我需要创建 3 个整数类型的新列来保存这 3 个数字。

示例数据如下所示。

示例数据如下所示

此外，数据也可以包含一些字符串值，但它固定为最后附加的一个字符串，用连字符 (-) 分隔。例如 1.2.1-xyz。如果数据是这样的，字符串值应该转到第四列。

此案例数据如下所示：

在此处输入图像描述

这是我为实现此要求而编写的查询。

请在 SQLFiddle http://sqlfiddle.com/#!9/5aaeb4/1下方找到

有没有更好的方法来做到这一点？我是数据库方面的新手，所以请提出一些优化的方法来实现这一点。

标签： mysqloracle

解决方案

在 MySQL 中，我们可以使用Substring_Index()函数以及用户定义的变量。变量帮助我们避免一次又一次地重做某些字符串操作。

架构（MySQL v5.7）

CREATE TABLE IF NOT EXISTS `table1` (
  `mainCol` varchar(45) NOT NULL 
) DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`mainCol`) VALUES
  ('1.2.1'),
  ('0.2'),
  ('1'), 
  ('1.2.1-xyz'),
  ('1.2');

询问

SELECT 
  mainCol, 

  @first := SUBSTRING_INDEX(mainCol, '.', 1) AS col1, 

  TRIM(LEADING '.' FROM 
       REPLACE(@second := SUBSTRING_INDEX(mainCol, '.', 2), 
               @first,
               '')
      ) AS col2,  

  @fourth := CASE WHEN INSTR(mainCol, '-') 
                  THEN SUBSTRING_INDEX(mainCol, '-', -1)
                  ELSE ''
             END AS col4, 

  REPLACE(TRIM(LEADING '.' FROM 
               REPLACE(SUBSTRING_INDEX(mainCol, '.', 3), 
                       @second,
                       '')
               ), 
          CONCAT('-', @fourth), 
          '') AS col3 
FROM table1
CROSS JOIN (SELECT @first := '', 
                   @second := '', 
                   @third := '',
                   @fourth := '') AS user_vars;

结果

| mainCol   | col1 | col2 | col4 | col3 |
| --------- | ---- | ---- | ---- | ---- |
| 1.2.1     | 1    | 2    |      | 1    |
| 0.2       | 0    | 2    |      |      |
| 1         | 1    |      |      |      |
| 1.2.1-xyz | 1    | 2    | xyz  | 1    |
| 1.2       | 1    | 2    |      |      |

在 DB Fiddle 上查看

推荐阅读