create table tb(name varchar2(10),subject varchar2(10),score number(10));
insert into tb values('张三','语文',88);
insert into tb values('张三','数学',87);
insert into tb values('张三','英语',86);
insert into tb values('李四','语文',66);
insert into tb values('李四','数学',67);
insert into tb values('李四','英语',68);
select * from tb;
--原表查询
select name as 姓名,max(case when subject='语文' then score else 0 end) as 语文 ,
max(case when subject='数学' then score else 0 end) as 数学 ,
max(case when subject='英语' then score else 0 end) as 英语
from tb
group by name
在各种数据库中,判断字段为空的方法
一:informix
select (case name when null then '2' else name end ) as name from ce_shi where id in (1,2,3)
二:oracle
select (case when name is null then '2' else name end ) as name from ce_shi where id in (1,2,3)
注意:oracle中null和空有区别
或者:select nvl(name,'2') from ce_shi
三:mysql
select (case when name is null then '2' else name end ) as name from ce_shi where id in (1,2,3)