还是多表查询
rankx 2011-12-05 03:30:03 create table tb_employee (
EmpID varchar2(20) ,
username varchar2(20) unique not null,
password varchar2(20) not null,
realName VARCHAR2(20) not null,
Sex number,
Age NUMBER,
tel NUMBER,
Address VARCHAR2(50),
DeptID varchar2(20) references tb_Department(DeptID),
power varchar2(20) default'normal',
constraint PK_EMPLOYEE primary key (EmpID)
);
create table tb_department (
DeptID varchar2(20) not null,
deptName VARCHAR2(20) not null,
deptAllowance NUMBER not null,
Manager VARCHAR2(20) not null,
deptTel NUMBER not null,
constraint PK_DEPARTMENT primary key (DeptID)
);
create table tb_salary (
salID varchar2(20) primary key,
EmpID varchar2(20) unique references tb_employee(EmpID),
Salary NUMBER,
bonus NUMBER,
deduct NUMBER default 0
);
--加班
create table tb_Extrawork (
ewID varchar2(20) primary key,
EmpID varchar(20) references tb_employee(EmpID),
EwDate DATE ,
EwConut NUMBER not null
);
--考勤
create table tb_Attendance (
attID varchar2(20) primary key,
AttDate DATE,
EmpID varchar2(20) references tb_employee(EmpID),
ConutAtt NUMBER
);
我想查只要出现在工资表中的员工不管有没有加班或者没有考勤的都查出来没有值的字段nvl('',0)让它值为0
我发现我对多表连接查询并不懂还希望你们给我点学习建议,谢谢
----------------
EmpID Salary bonus deduct EwDate AttDate ConutAtt deptAllowance EwConut
以下是我改dzntree同志的
select tm.empid,ts.salary,ts.bonus,ts.deduct,te.ewdate,te.ewconut,ta.attdate,ta.conutatt,td.deptallowance
from
tb_employee tm
LEFT JOIN
tb_department td
on td.deptid=tm.deptid
LEFT JOIN
tb_salary ts
on tm.empid=ts.empid
LEFT JOIN
tb_Extrawork te
on tm.empid=te.empid
LEFT JOIN
tb_Attendance ta
on tm.empid=ta.empid
where ts.empid = te.empid
and te.empid = ta.empid
and to_char(te.EwDate,'MM') = to_char(ta.AttDate,'MM')
and to_char(ta.AttDate,'MM')=to_char(to_date('2011-10-1','yyyy-mm-dd'),'MM')
and to_char(ta.AttDate,'MM')=to_char(to_date(sysdate,'MM')
and tm.empid=tm.empid
order by te.EwDate;