数据准备

基础表创建

/* 学生表 */
CREATE TABLE student (
    sid VARCHAR(16),
    sname VARCHAR(20)
);
/* 课程表 */
CREATE TABLE course (
    cid VARCHAR(20),
    cname VARCHAR(100)
);
/* 成绩表 */
CREATE TABLE score (
    sid VARCHAR(16),
    cid VARCHAR(20),
    val FLOAT
);

数据导入

/* 学生表数据 */
insert into student (sid, sname) values('1001', '张三');
insert into student (sid, sname) values('1002', '李四');
insert into student (sid, sname) values('1003', '赵二');
insert into student (sid, sname) values('1004', '王五');
insert into student (sid, sname) values('1005', '刘青');
insert into student (sid, sname) values('1006', '周明');
/* 课程表数据 */
insert into course (cid, cname) values('C001', '大学语文');
insert into course (cid, cname) values('C002', '新视野英语');
insert into course (cid, cname) values('C003', '离散数学');
insert into course (cid, cname) values('C004', '概率论与数理统计');
insert into course (cid, cname) values('C005', '线性代数');
insert into course (cid, cname) values('C006', '高等数学');
/*成绩表数据*/
insert into score(sid, cid, val) values('1001', 'C001', 67);
insert into score(sid, cid, val) values('1002', 'C001', 68);
insert into score(sid, cid, val) values('1003', 'C001', 69);
insert into score(sid, cid, val) values('1004', 'C001', 70);
insert into score(sid, cid, val) values('1005', 'C001', 71);
insert into score(sid, cid, val) values('1006', 'C001', 72);
insert into score(sid, cid, val) values('1001', 'C002', 87);
insert into score(sid, cid, val) values('1002', 'C002', 88);
insert into score(sid, cid, val) values('1003', 'C002', 89);
insert into score(sid, cid, val) values('1004', 'C002', 90);
insert into score(sid, cid, val) values('1005', 'C002', 91);
insert into score(sid, cid, val) values('1006', 'C002', 92);
insert into score(sid, cid, val) values('1001', 'C003', 83);
insert into score(sid, cid, val) values('1002', 'C003', 84);
insert into score(sid, cid, val) values('1003', 'C003', 85);
insert into score(sid, cid, val) values('1004', 'C003', 86);
insert into score(sid, cid, val) values('1005', 'C003', 87);
insert into score(sid, cid, val) values('1006', 'C003', 88);
insert into score(sid, cid, val) values('1001', 'C004', 88);
insert into score(sid, cid, val) values('1002', 'C004', 89);
insert into score(sid, cid, val) values('1003', 'C004', 90);
insert into score(sid, cid, val) values('1004', 'C004', 91);
insert into score(sid, cid, val) values('1005', 'C004', 92);
insert into score(sid, cid, val) values('1006', 'C004', 93);
insert into score(sid, cid, val) values('1001', 'C005', 77);
insert into score(sid, cid, val) values('1002', 'C005', 78);
insert into score(sid, cid, val) values('1003', 'C005', 79);
insert into score(sid, cid, val) values('1004', 'C005', 80);
insert into score(sid, cid, val) values('1005', 'C005', 81);
insert into score(sid, cid, val) values('1006', 'C005', 82);
insert into score(sid, cid, val) values('1001', 'C006', 79);
insert into score(sid, cid, val) values('1002', 'C006', 78);
insert into score(sid, cid, val) values('1003', 'C006', 77);
insert into score(sid, cid, val) values('1004', 'C006', 83);
insert into score(sid, cid, val) values('1005', 'C006', 84);
insert into score(sid, cid, val) values('1006', 'C006', 85);

静态行转列

select st.sid, st.sname, 
    sum(CASE c.cname WHEN '大学语文' THEN ifnull(s.val,0) ELSE 0 END ) "大学语文",
    sum(CASE c.cname WHEN '新视野英语' THEN ifnull(s.val,0) ELSE 0 END ) "新视野英语", 
    sum(CASE c.cname WHEN '离散数学' THEN ifnull(s.val,0) ELSE 0 END ) "离散数学",
    sum(CASE c.cname WHEN '概率论与数理统计' THEN ifnull(s.val,0) ELSE 0 END ) "概率论与数理统计",
    sum(CASE c.cname WHEN '线性代数' THEN ifnull(s.val,0) ELSE 0 END ) "线性代数",
    sum(CASE c.cname WHEN '高等数学' THEN ifnull(s.val,0) ELSE 0 END ) "高等数学"
from student st
left join score s On st.sid = s.sid
left join course c On c.cid = s.cid
group by st.sid, st.sname;

动态行转列

列语句生成，版本>=8.1.2，使用GROUP_CONCAT

SELECT group_concat(
    concat('sum(IF(c.cname = ''', c.cname, ''', s.val, 0)) AS "', c.cname, '"')
)
FROM course c;

列语句生成，版本<8.1.2不支持GROUP_CONCAT，暂且用LISTAGG替代

SELECT listagg(
    concat('sum(IF(c.cname = ''', c.cname, ''', s.val, 0)) AS "', c.cname, '"'),
',') within GROUP(ORDER BY 1)
FROM course c;

新建/重建视图，可以用函数

CREATE OR REPLACE FUNCTION build_view()
RETURNS VOID
LANGUAGE plpgsql
AS $$ DECLARE 
sql text;
rec record;
BEGIN
    sql := 'select LISTAGG(
	CONCAT( ''sum(IF(c.cname = '''''', c.cname, '''''', s.val, 0)) AS "'', c.cname, ''"'' )
	,'','' ) within group(order by c.cid) from course c;';
	EXECUTE sql INTO rec;
	sql := 'drop view if exists get_sum';
	EXECUTE sql;
	sql := 'create view get_sum as select st.sid, st.sname, ' || rec.LISTAGG || 'from student st 
    left join score s On st.sid = s.sid
    left join course c On c.cid = s.cid
    group by st.sid, st.sname';
	EXECUTE sql;
END$$;
call build_view();

新建/重建视图，也可以用匿名块

DECLARE 
sql text;
rec record;
BEGIN
    sql := 'select LISTAGG(
	CONCAT( ''sum(IF(c.cname = '''''', c.cname, '''''', s.val, 0)) AS "'', c.cname, ''"'' )
	,'','' ) within group(order by c.cid) from course c;';
	EXECUTE sql INTO rec;
	sql := 'drop view if exists get_sum';
	EXECUTE sql;
	sql := 'create view get_sum as select st.sid, st.sname, ' || rec.LISTAGG || 'from student st 
    left join score s On st.sid = s.sid
    left join course c On c.cid = s.cid
    group by st.sid, st.sname';
	EXECUTE sql;
END;
/

执行视图查询

select * from get_sum;

–
想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料哦~