mysql - 如何使用 MySql 将列转换为表的行

Question

我有如下表格。

id      |  name   |   place_1  |  Place_2  |  place_3  |
------------------------------------------------------
12      | Anne    |   School   |           |           |
13      | Smith   |            | church    |           |
14      | Mari    |  School    |           |  Temple   |
15      | Nash    |            |           |  Temple   |
16      | Narmada |            | Church    |  Temple   |

我想要做的是分别计算位置并得到一个只有以下列的表格。就像 Place_1 , Place_2 , Place_3 一样。并使用存储过程将列转换为行。

喜欢下面

Place   | count |
------------------
Place_1 | 2     |
Place_2 | 2     |
Place_3 | 3     | 

谁能帮我做这件事？

Answer 1

drop table if exists t;
create table t(id int, name varchar(10), place_1 varchar(10),  Place_2 varchar(10),  place_3  varchar(10));
insert into t values
(2       , 'Anne'    ,  ' School'   , null      ,    null     ), 
(13      , 'Smith'   ,  null        , 'church'  ,    null     ), 
(14      , 'Mari'    ,  'School'    , null      ,    'Temple' ),  
(15      , 'Nash'    ,  null        , null      ,    'Temple' ),  
(16      , 'Narmada' ,  null        , 'Church'  ,    'Temple' );


select   'Place_1' as place,count(*) 'Count' from t where place_1 is not null
union
select   'Place_2' as place,count(*) from t where place_2 is not null
union
select   'Place_3' as place,count(*) from t where place_3 is not null

结果

+---------+-------+
| place   | Count |
+---------+-------+
| Place_1 |     2 |
| Place_2 |     2 |
| Place_3 |     3 |
+---------+-------+