小小的、奇怪的sql查询语句

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)