在一个test表里面字段和数据如下：
name teacher class price
初级会计务实 李真 精讲班 200
初级会计务实 李真 通关班 300
初级会计务实 李真 协议班 800
经济法基础 陆宝 精讲班 200
经济法基础 陆宝 通关班 300
经济法基础 陆宝 协议班 800
我想查询出这样的效果：
课程 老师 精讲班 通关班 协议班
初级会计务实 李真 200 300 800
经济法基础 陆宝 200 300 800
本人小菜鸟，在线等大神的帮助
------解决方案--------------------
SELECT name, teacher, sum("精讲班"), sum("通关班"), sum("协议班")
FROM (select name,
teacher,
case
when a.class = '精讲班' then
a.price
END "精讲班",
case
when a.class = '通关班' then
a.price
END "通关班",
case
when a.class = '协议班' then
a.price
END "协议班"
from test a) b
group by name, teacher;
------解决方案--------------------
SELECT name, teacher, sum("精讲班"), sum("通关班"), sum("协议班")
FROM (select name,
teacher,
case
when a.class = '精讲班' then
a.price
END "精讲班",
case
when a.class = '通关班' then
a.price
END "通关班",
case
when a.class = '协议班' then
a.price
END "协议班"
from test_1 a) b
group by name, teacher;
------解决方案--------------------
with temp1 as (select distinct name,teacher from test)
select name as 课程,teacher as 老师,
(select price from test where name=a.name and teacher=a.teacher and class='精讲班') as 精讲班,
(select price from test where name=a.name and teacher=a.teacher and class='通关班') as 通关班,
(select price from test where name=a.name and teacher=a.teacher and class='协议班') as 协议班
from temp1 a