点击刷新
列转行上一篇博客已经介绍过了。
下面介绍一下行转列的实现
假设我们有一个数据表：
CREATE TABLE row_to_line ( user_name character varying(30) NOT NULL, -- 学生名称 yingyu integer, -- 得分 yuwen integer, huaxue integer, wuli integer, CONSTRAINT row_to_line_pkey PRIMARY KEY (user_name) ); insert into row_to_line select 'liqiu', 80, 90, 90, 89; insert into row_to_line select 'lingling', 89, 99, 100, 90; insert into row_to_line select 'xingxing', 90, 94, 97, 99;
显示如下：
那么我们想要将它转化为一列列的如下结果输出：
那么如何做到哪？
方法一、简单可读性强：
select a.user_name, a.title, a.score from ( (select user_name, yingyu as "score", 'yingyu' as title from row_to_line) union (select user_name, yuwen as "score", 'yuwen' as title from row_to_line) union (select user_name, huaxue as "score", 'huaxue' as title from row_to_line) union (select user_name, wuli as "score", 'wuli' as title from row_to_line) ) a order by a.user_name, a.title
方法二、快速
这是pg的专有方法
select * from tmp.dim_values_20170821 limit 10
select lower(regexp_split_to_table(dim_values, ' ')) as dim_name, table_name,all_num from tmp.dim_values_20170821 where table_name = '景区统计表'
© 著作权归作者所有
举报
发表评论
0/200
