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

来源：互联网发布：老鼠走迷宫算法编辑：程序博客网时间：2024/06/07 04:06

create table tb(name varchar2(10),subject varchar2(10),scorenumber(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;

--原表查询

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

select name as 姓名,max(case when subject='语文' then score else 0end) 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

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

在各种数据库中，判断字段为空的方法

一：informix

select (case name when null then '2' else name end ) as namefrom ce_shi where id in (1,2,3)
二：oracle

select (case when name is null then '2' else name end ) as namefrom 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 namefrom ce_shi where id in (1,2,3)

0 0