MyException - 我的异常网
当前位置:我的异常网» Oracle技术 » 连接查询有关问题

连接查询有关问题

www.MyException.Cn  网友分享于:2013-12-23  浏览:13次
连接查询问题
有两个表

create table tb_sal(
EmpID varchar2(20) unique references tb_employee(EmpID),
Salary NUMBER,
bonus NUMBER,
deduct NUMBER default 0
);


create table tb_Extrawork (
  EmpID varchar(20) references tb_employee(EmpID) not null,
  EwDate DATE ,
  EwConut NUMBER not null,
  constraint PK_EXTRAWORK primary key (EmpID)
);


insert into tb_sal(empid,Salary,bonus,deduct) values('e1001',5000,1000,0);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1002',5000,1000,0);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1003',5000,1000,0);
insert into tb_sal(empid,Salary,bonus,deduct) values('e1004',5000,1000,0);


insert into tb_extrawork(EmpID,ewdate,ewconut) values('e1001',to_date('2011-10-01','yyyy-mm-dd'),5);
insert into tb_extrawork(EmpID,ewdate,ewconut) values('e1002',to_date('2011-10-01','yyyy-mm-dd'),5);
insert into tb_extrawork(EmpID,ewdate,ewconut) values('e1003',to_date('2011-10-01','yyyy-mm-dd'),5);

其中我想查询效果如下 

empid Salary bonus deduct ewdate ewconut
e1004 5000 1000 0 0 0


------解决方案--------------------
select t1.empid, t1.Salary, t1.bonus, t1.deduct, t2.ewdate, t2.ewconut
from table tb_sal t1, tb_Extrawork t2
where t1.empid = t2.EmpID

反正你的EmpID是主key,又是外键
------解决方案--------------------
全外连接
select * where tb_sal t1 full join tb_Extrawork t2 on t1.empid = t2.EmpID

------解决方案--------------------
不就是left join加nvl吗
select t1.empid, t1.Salary, t1.bonus, t1.deduct, nvl(t2.ewdate,0), nvl(t2.ewconut,0)
from table tb_sal t1
left join tb_Extrawork t2 on t1.empid = t2.EmpID
where t1.empid='e1004'
;

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有