基于Oracle5.6、MySQL 11和Oracle12中的现有列填充列

Question

我有一个表，其中一列是varchar类型，它包含的值为'1.2.1'，'1'，'0.2‘等。

通常最多有3个用小数(.)分隔的数字

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

示例数据如下所示。

​

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

此案例数据如下所示：

​

​

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

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

有没有更好的方法来做这件事？我是DB方面的新手，所以请建议一些优化的方法来实现这一点。

Answer 1

在MySQL中，我们可以使用Substring_Index()函数，也可以使用User-defined variables。变量帮助我们避免一次又一次地重复某些字符串操作。

模式(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    |      |      |

View on DB Fiddle