连接查询问题
有两个表
create table tb_sal(
EmpIDvarchar2(20) unique references tb_employee(EmpID),
Salary NUMBER,
bonusNUMBER,
deductNUMBER default 0
);
create table tb_Extrawork (
EmpID varchar(20) references tb_employee(EmpID) not null,
EwDate DATE ,
EwConutNUMBERnot 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'
;
