create table kecheng
(id NUMBER,
name VARCHAR2(20),
course VARCHAR2(20),
score NUMBER
);
insert into kecheng (id, name, course, score)
values (1, '张三', '语文', 67);
insert into kecheng (id, name, course, score)
values (1, '张三', '数学', 76);
insert into kecheng (id, name, course, score)
values (1, '张三', '英语', 43);
insert into kecheng (id, name, course, score)
values (1, '张三', '历史', 56);
insert into kecheng (id, name, course, score)
values (1, '张三', '化学', 11);
insert into kecheng (id, name, course, score)
values (2, '李四', '语文', 54);
insert into kecheng (id, name, course, score)
values (2, '李四', '数学', 81);
insert into kecheng (id, name, course, score)
values (2, '李四', '英语', 64);
insert into kecheng (id, name, course, score)
values (2, '李四', '历史', 93);
insert into kecheng (id, name, course, score)
values (2, '李四', '化学', 27);
insert into kecheng (id, name, course, score)
values (3, '王五', '语文', 24);
insert into kecheng (id, name, course, score)
values (3, '王五', '数学', 25);
insert into kecheng (id, name, course, score)
values (3, '王五', '英语', 8);
insert into kecheng (id, name, course, score)
values (3, '王五', '历史', 45);
insert into kecheng (id, name, course, score)
values (3, '王五', '化学', 1);
commit;
select * from kecheng;
1 Decode方式
SELECT ID,NAME,
SUM(DECODE(course,'语文',score,0)) 语文,--这里使用max,min都可以
SUM(DECODE(course,'数学',score,0)) 数学,
SUM(DECODE(course,'英语',score,0)) 英语,
SUM(DECODE(course,'历史',score,0)) 历史,
SUM(DECODE(course,'化学',score,0)) 化学
FROM kecheng GROUP BY ID ,NAME;
2 Case方式
SELECT ID,NAME,
MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,
MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,
MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学
FROM kecheng GROUP BY ID ,NAME;
3 使用wmsys.wm_concat用法
SELECT NAME,wmsys.wm_concat(course||score)
FROM kecheng group by name;
4 使用PL/SQL
DECLARE
--存放最终的SQL
LV_SQL VARCHAR2(3000);
--存放连接的SQL
SQL_COMMOND VARCHAR2(3000);
--定义游标
CURSOR CUR IS
SELECT COURSE FROM KECHENG GROUP BY COURSE;
BEGIN
--定义查询开头
SQL_COMMOND := 'SELECT NAME ';
FOR I IN CUR LOOP
--将结果相连接
SQL_COMMOND := SQL_COMMOND || ' ,SUM(DECODE(course,''' || I.COURSE ||
''',score,0)) ' || I.COURSE;
DBMS_OUTPUT.PUT_LINE(SQL_COMMOND);
END LOOP;
SQL_COMMOND := SQL_COMMOND || ' from KECHENG group by name';
LV_SQL := 'INSERT INTO temp_ss ' || SQL_COMMOND;
DBMS_OUTPUT.PUT_LINE(LV_SQL);
EXECUTE IMMEDIATE LV_SQL;
END;
5 使用 union all
with a as
union all
select '2' 学号,'23' 语文,'' 数学,'' 英语,'25' 物理 from dual
union all
select '3' 学号,'23' 语文,'12' 数学,'24' 英语,'' 物理 from dual)
select 学号,课目,成绩 from(
select 学号,语文 as 成绩,'语文' 课目 from a
union all
select 学号,数学 as 成绩,'数学' 课目 from a
union all
select 学号,英语 as 成绩,'英语' 课目 from a
union all
select 学号,物理 as 成绩,'物理' 课目 from a
)