如何从初始行创建多行 [英] How to create multiple rows from a initial row

问题描述

我用的是mysql db engine，我想知道有没有可能把表中的数据一行转移到另一个表中，这个表会由两列组成，id和value每个传输的值都将进入一行，行看起来像 ID、值，并且只要它有一个传输到新行的值，只要它有一个属于 id 的值，就会维护该 id它从中转移的一行

I use mysql db engine, I wonder is it possible that the data in the table one row transferred to another table, this table would consist of two columns, id and value each of the transferred value would go into one row and row would look like ID, value, and for as long as it has a value that is transferred to new row maintains the id as long as it has a value that belonged to the id of a row from which it transferred

初始表格看起来像

id  |country_name   |city_1      |city_2      |city_3      |city_4
------------------------------------------------------------------------
1   |Some_country   |some_city1  |some_city2  |some_city3  |some_city4

想要的桌子看起来像

 id | city_name
 1  |  some_city1
 1  |  some_city2
 1  |  some_city3
 1  |  some_city4

推荐答案

将此用于一个特定的 ID

select id, city_name from(
    select id, city_1 as city_name from yourTable    
    union all
    select id, city_2 from yourTable    
    union all
    select id, city_3 from yourTable    
    union all
    select id, city_4 from yourTable
) as t where id= yourID

http://sqlfiddle.com/#!9/7ee1f/1

用于整个表格

 select id, city_name from(
    select id, city_1 as city_name from yourTable    
    union all
    select id, city_2 from yourTable    
    union all
    select id, city_3 from yourTable    
    union all
    select id, city_4 from yourTable
) as t
order by id

这篇关于如何从初始行创建多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！