select COALESCE(total.fab, preborrow.fab,'-') fab,nvl(total.qty,0) total,nvl(borrow.qty,0) borrow,nvl(idle.qty,0) idle,nvl(overdue.qty,0)
overdue,nvl(preborrow.qty,0) preborrow from(select fab,count(*) qty from mpb_camera_r where fab is not null and is_active='Y' and camera_type=Camera group by fab ) total left join (select fab,count(*) qty from mpb_camera_r where fab is not null and is_active='Y' and camera_type=Camera and status='Using' group by fab) borrow on total.fab=borrow.fab left join (select fab,count(*) qty from mpb_camera_r where fab is not null and is_active='Y' and camera_type=Camera and status='Idle' group by fab) idle on total.fab=idle.fab
"full join (select fab,count(*) qty from mpb_preborrow_r where fab is not null and status='PRE' and equipment_type=Camera group by fab) preborrow on total.fab=preborrow.fab ";
1:select字句的《目标列表达式》 不仅可以是表中的属性列也可也是表达式
如: select 2015—age from student 得到学生出生年份
用户可以指定别名来改变查询结果的列标题 这对于含算术表达式,常量,函数名的目标列表达式尤为重要
select sname Name,'Year of Birth' Brith,2015-age Brithday,Lower(sdept) Department from student;
2:函数COALESCE :
3:nvl:
4:left join
5:full join
6:count(*)