mysql 通过表查找将逗号分隔的列值与新值映射

Question

我正在使用 MySQL 数据库。

我有一个 CUST_INV_DET 表，其中包含以下格式的数据

---------------------------
CUSTOMER_ID   CUSTOMER_NO
---------------------------
     1        1983,1988,1989
     2        2014,2011,2010
     3        3012,3059,3045 
---------------------------

还有另一张 table 。 NEW_CUSTOMER_NO_FORMAT

--------------------------------------
OLD_CUSTOMER_NO      NEW_CUSTOMER_NO
--------------------------------------
1983                    C1983-01
1988                    C1988-03
1989                    C1989-06
2014                    C2014-01
2011                    C2011-02
2010                    C2010-02
3012                    C3012-03
3059                    C3059-23
3045                    C3045-09

任何人都可以建议我如何通过从 NEW_CUSTOMER_NO_FORMAT 表中查找来以新格式映射 CUST_INV_DET.CUSTOMER_NO。

我知道我需要使用光标，但在下面的光标部分中，我不知道如何映射，因为它是逗号分隔的值。

我需要在 NO_MORE_DATA =0 内再添加一个光标吗？

IF (NO_MORE_DATA = 0) THEN

  //MAP OLD TO NEW FORMAT HERE

END IF;

下面是插入脚本

CREATE TABLE CUST_INV_DET(
  CUSTOMER_ID INT(11) AUTO_INCREMENT PRIMARY KEY, 
  CUSTOMER_NO VARCHAR(500)
);

INSERT INTO CUST_INV_DET(CUSTOMER_NO) VALUES
('1983,1988,1989'),
('2014,2011,2010'),
('3012,3059,3045');

CREATE TABLE NEW_CUSTOMER_NO_FORMAT (
  OLD_CUSTOMER_NO VARCHAR(500),
  NEW_CUSTOMER_NO VARCHAR(500)
);

INSERT INTO NEW_CUSTOMER_NO_FORMAT VALUES
('1983','C1983-01'),
('1988','C1988-03'),
('1989','C1989-06'),
('2014','C2014-01'),
('2011','C2011-02'),
('2010','C2010-02'),
('3012','C3012-03'),
('3059','C3059-23'),
('3045','C3045-09');

下面是我正在寻找的最终输出

CUSTOMER_ID           CUSTOMER_NO                    
1                     'C1983-01,C1988-03,C1989-06'
2                     'C2014-01,C2011-02,C2010-02'
3                     'C3012-03,C3059-23,C3045-09'

Answer 1

select 
CUSTOMER_ID, 
substring(Customer_no,0,charindex(',',Customer_no,1)) col1,
substring(Customer_no,charindex(',',Customer_no,1) + 1,charindex(',',Customer_no,1)-1) col2,
substring(Customer_no,charindex(',',Customer_no,1) + len(substring(Customer_no,charindex(',',Customer_no,1) + 1,charindex(',',Customer_no,1)-1) )+2,charindex(',',Customer_no,1)-1) col3
into #Temp2
from CUST_INV_DET