MySQL行列轉換拼接

智慧先行者發表於2015-07-20
MySql
mysql> select TBL_ID,CREATE_TIME,LAST_ACCESS_TIME,TBL_NAME,TBL_TYPE from TBLS;
+--------+-------------+------------------+----------------------+---------------+
| TBL_ID | CREATE_TIME | LAST_ACCESS_TIME | TBL_NAME             | TBL_TYPE      |
+--------+-------------+------------------+----------------------+---------------+
|      6 |  1437309077 |                0 | students             | MANAGED_TABLE |
|     11 |  1437402612 |                0 | user_info_bucketed_1 | MANAGED_TABLE |
+--------+-------------+------------------+----------------------+---------------+
2 rows in set (0.00 sec)

mysql> select TBL_ID,CREATE_TIME,LAST_ACCESS_TIME,TBL_NAME,TBL_TYPE from TBLS \G;
*************************** 1. row ***************************
          TBL_ID: 6
     CREATE_TIME: 1437309077
LAST_ACCESS_TIME: 0
        TBL_NAME: students
        TBL_TYPE: MANAGED_TABLE
*************************** 2. row ***************************
          TBL_ID: 11
     CREATE_TIME: 1437402612
LAST_ACCESS_TIME: 0
        TBL_NAME: user_info_bucketed_1
        TBL_TYPE: MANAGED_TABLE
2 rows in set (0.00 sec)
 
mysql> select * from user_info;
+---------+-----------+-----------+
| user_id | firstname | lastname  |
+---------+-----------+-----------+
|     100 | Hadoop    | Spark01   |
|     100 | Hadoop    | Spark02   |
|     100 | Hadoop    | Spark03   |
|     200 | Hive      | Python2.6 |
|     200 | Hive      | Python2.7 |
|     200 | Hive      | Python3.3 |
|     200 | Hive      | Python3.4 |
|     300 | HBase     | Pig       |
|     300 | HBase     | Zoo       |
+---------+-----------+-----------+
9 rows in set (0.00 sec)
 
mysql> select user_id,group_concat(firstname) from user_info group by user_id;
+---------+-------------------------+
| user_id | group_concat(firstname) |
+---------+-------------------------+
|     100 | Hadoop,Hadoop,Hadoop    |
|     200 | Hive,Hive,Hive,Hive     |
|     300 | HBase,HBase             |
+---------+-------------------------+
3 rows in set (0.06 sec)

mysql> select user_id,group_concat(lastname) from user_info group by user_id;
+---------+-----------------------------------------+
| user_id | group_concat(lastname)                  |
+---------+-----------------------------------------+
|     100 | Spark01,Spark02,Spark03                 |
|     200 | Python2.6,Python2.7,Python3.3,Python3.4 |
|     300 | Pig,Zoo                                 |
+---------+-----------------------------------------+
3 rows in set (0.00 sec)
 
mysql> select user_id,group_concat(lastname separator ';') from user_info group by user_id;
+---------+-----------------------------------------+
| user_id | group_concat(lastname separator ';')    |
+---------+-----------------------------------------+
|     100 | Spark01;Spark02;Spark03                 |
|     200 | Python2.6;Python2.7;Python3.3;Python3.4 |
|     300 | Pig;Zoo                                 |
+---------+-----------------------------------------+
3 rows in set (0.00 sec) 

mysql> select user_id,group_concat(lastname order by lastname desc separator '#') from user_info group by user_id;
+---------+-------------------------------------------------------------+
| user_id | group_concat(lastname order by lastname desc separator '#') |
+---------+-------------------------------------------------------------+
|     100 | Spark03#Spark02#Spark01                                     |
|     200 | Python3.4#Python3.3#Python2.7#Python2.6                     |
|     300 | Zoo#Pig                                                     |
+---------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

 

相關文章