Merge multiple query results into one column in MySQL/MariaDB

Recently, a query requirement is how to display a column when multiple select columns -- for example, if the table contains three columns and two rows:

1 2 3
4 5 6
How to convert query results into one column and multiple rows:

1
2
3
4
5
6
First, create a test table.

Create table test (c1 int, c2 int, c3 int );
Insert into test values (, 3), (, 6), (, 9), (, 12 );

 

Method 1: Use union

Select c1 from test union select c2 from test union select c3 from test;

 

This method is the easiest to understand, that is, to query three times, one column at a time, and then merge; but the disadvantage is to scan the table three times.

Method 2: insert a line break

Mysql-Bse "select concat (c1, '@', c2, '@', c3) from test;" | sed's/@/\ n/g'

 

This method is also easy to understand. Line breaks are inserted between fields. Compared with the first method, you only need to access the table once. The disadvantage is that you need to use an external text processing tool.

Method 3: Use case

Select case when rownum = 1 then c1 when rownum = 2 then c2 when rownum = 3 then c3 else ''end from test, (SELECT @ rownum: = @ rownum + 1 AS rownum FROM (SELECT @ rownum: = 0) r, test) B;

 

This method is intended by my colleague shengkaisu. It is hard to understand: generate row number rownum, and then generate the row number table and old table for Cartesian product. If rownum is 1, select the first column, if rownum is 2, select the second column ...........