--建表
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 t;
--2.1、Decode方式
SELECT ID,NAME,
SUM(DECODE(course,'语文',score,0)) 语文,
SUM(DECODE(course,'数学',score,0)) 数学,
SUM(DECODE(course,'英语',score,0)) 英语,
SUM(DECODE(course,'历史',score,0)) 历史,
SUM(DECODE(course,'化学',score,0)) 化学,
sum(decode(course,'地理',score,0)) 地理,
sum(score) 总分
FROM kecheng
GROUP BY ID ,NAME
--2.2、Case方式
--这里使用max也可以，但是使用min和avg就不行了
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) 化学,
max(case when course='' then score else 0 end ) 无课 ,
sum(score) 总分
FROM kecheng
GROUP BY ID ,NAME
--结果与上方一样
--2.3、wmsys.wm_concat行列转换函数（该函数只能在group by语句里）
SELECT ID,NAME,
wmsys.wm_concat(course || ':'||score) course
FROM kecheng
GROUP BY ID ,NAME;
--2.4、使用over(partition by t.u_id)用法
SELECT NAME, max(r) from (
SELECT name,wmsys.wm_concat(course ||score) OVER (PARTITION BY NAME order by course) r
FROM kecheng
) group by name
---三、动态转换
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;