MySQL 语句在不使用数据透视函数或联合的情况下对表进行数据透视

Question

在不使用 pivot/unpivot 函数或联合的情况下，是否可以更改此数据:

+----+--------+---------+
| id | name   | surname |
+----+--------+---------+
| 1  | john   | smith   |
| 2  | jack   | brown   |
+----+--------+---------+

进入这个:

+----+-------------+
| id | data        |
+----+-------------+
| 1  | john        |
| 1  | smith       |
| 2  | jack        |
| 2  | brown       |
+----+-------------+

Answer 1

你可以使用光标

drop procedure if exists p;
delimiter $$
CREATE  PROCEDURE `p`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin 
    declare vid int(8);
    declare vname varchar(15);
    declare vsurname varchar(15);
    declare done int default 0;
    declare cname cursor for select id,name,surname from t id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cname;
    cursorloop:loop
    fetch cname into vid,vname,vsurname;
    if done = true then  
        leave cursorloop;
    end if;  
    insert into t1 (id,name) select vid,vname;
    insert into t1 (id,name) select vid,vsurname;
    end loop cursorloop;
    close cname;
end $$
delimiter ;

drop table if exists t,t1;
create table t( id int, name varchar(20), surname varchar(20));
create table t1(id int, name varchar(20));
insert into t values
( 1  , 'john'   , 'smith'),
( 2  , 'jack'   , 'brown');

call p();

MariaDB [sandbox]> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | john  |
|    1 | smith |
|    2 | jack  |
|    2 | brown |
+------+-------+
4 rows in set (0.00 sec)

但是为什么不使用联合呢？