当前位置: 代码迷 >> Oracle管理 >> 请教这个sql语句如何写啊
  详细解决方案

请教这个sql语句如何写啊

热度:89   发布时间:2016-04-24 05:03:10.0
请问这个sql语句怎么写啊?
在一个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
  相关解决方案