时间戳

首页 > 解决方案 > 基于标准表MySql从原始数据列拆分/提取到多个列

mysql - 基于标准表MySql从原始数据列拆分/提取到多个列

问题描述

我有 2 张桌子

第一个地址表

Country State       city        ZIP code
India   Andhra      Vizag       535001
India   Andhra      Vijayawada  520001
India   Andhra      Tirupathi   530001
India   Karnataka   Bengalore   400120
India   Tamilnadu   Chennai     400001
USA     Newyork     Newyork     666601
USA     Newjersy    Newjersy    666501

第二个是客户表

Customer    Raw_address                          City   state   Country ZIP code
X01         D No:123VizagAndhraIndia                    
X02         #46,XYZ streetBengaloreKarnataka                    
X03         #123 Dash Street Newjersy

现在需要将客户表更新为

Customer    Raw_address                         City        state       Country ZIP code
X01         D No:123VizagAndhraIndia            Vizag       Andhra      India   535001
X02         #46,XYZ streetBengaloreKarnataka    Bengalore   Karnataka   India   400120
X03         #123 Dash Street Newjersy           Newjersy    Newjersy    USA     666501

我已经给出了用于表创建和值的 SQL 代码

CREATE TABLE `Address` (
    `country` VARCHAR(50),
    `State` VARCHAR(50),
    `City` VARCHAR(50),
    `Zip code` VARCHAR(6)
);

insert into Address(country, state,city,`zip code`)
values
('India','Andhra','Vizag',535001),
('India','Andhra','Vijayawada',520001),
('India','Andhra','Tirupathi',530001),
('India','Karnataka','Bengalore',400120),
('India','Tamilnadu','Chennai',400001),
('USA','Newyork','Newyork',666601),
('USA','Newjersy','Newjersy',666501);

CREATE TABLE `Customer` (
    `Customer` VARCHAR(8),
    `Raw_Address` VARCHAR(500),
    `City` VARCHAR(50),
    `State` VARCHAR(50),
    `Country` VARCHAR(50),
    `Zip code` VARCHAR(6)
);

insert into Customer (customer, raw_address)
values
('X01','D No:123VizagAndhraIndia'),
('X02','#46,XYZ streetBengaloreKarnataka'),
('X03','#123 Dash Street Newjersy');

我尝试了多种方式加入，但没有成功。

标签： mysql

解决方案

最后我想通了。

UPDATE customer c
JOIN address a ON c.Raw_Address LIKE CONCAT('%', a.city ,'%')
SET 
        c.city=a.City,
        c.state=a.State,
        c.Country=a.country, 
        c.`Zip code`=a.`Zip code`

推荐阅读