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;
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;
解决方案 »
- CRS-0184: Cannot communicate with the CRS daemon. RAC重启错误
- oracle 触发器
- exec sys.kill_session(883,65039);
- 超大数据量的实时处理
- 还是昨天的union链接问题,××××在线等待×××××
- 请问以下过程有哪些错误!!!100分。
- 将SQL-Server存储过程转换为Oracle的,谢谢大家~~~(2)
- 数据库中有一个aa用户,导出了该用户的所有,用system导入,怎样才能不导到system用户下,而是aa用户下
- sql server2000下建的数据库和写好的存储过程能导入Oracle里面来用吗?
- 两边都是Oracle816服务器,本地创建服务连接远端,测试通过,但是在SqlPlus和DBA studio中都登录不上!
- DBA oracle 數據消理, 把一些歷史資料轉到磁帶或光盤上
- 多表查询
/*简单逻辑关系整理
1 考勤肯定是月统计
2 部门津贴肯定也是按月发放
3 加班可能为工作日内可能为工作日外
4 销售额的产生肯定是在工作日内或加班时间内
5 参与考勤的员工为最大员工集合,可能有加班记录
所以一个月内的统计只能产拆分成两张统计表进行展示
*/
--月内(以11月为例)销售,加班,考勤情况表
--注 countAtt理解为某日内打卡次数,暂定义为,打卡一次或一次以上参与考勤select att.empID,service_date,nvl(attTimes,0) as attTimes,nvl(ewConut,0) as ewCount,nvl(salaryDay,0),nvl(bonusDay,0),nvl(deductDay,0) from
(
SELECT to_date('2011-11-01', 'YYYY-MM-DD') + ROWNUM - 1 service_date
FROM dual
CONNECT BY ROWNUM <= (to_date('2011-11-30', 'YYYY-MM-DD') -
to_date('2011-11-01', 'YYYY-MM-DD') + 1)
) a left join
(
select empID, attDate, count(1) as attTimes
from tb_Attendance
group by empID, attDate
having count(1) >= 1
) att on a.service_date = att.attDate
left join tb_Extrawork ew on a.service_date = ew.ewDate and att.empID = ew.empID
left join
(
--销售额一个工作日内可能产生多次,需要先进行分组统计,一般有日期字段
--但没有日期字段进行分组,无法不知道怎么写了.现假设有日期字段salaryDate
select empID,salaryDate,sum(salary) as salaryDay,sum(bonus) as bonusDay,sum(deduct) as deductDay
from tb_salary
group by empID,salaryDate
) sa on a.service_date = sa.salaryDate and att.empID = sa.salaryDate--上表(暂表名为:month_11)可以做为查询结果进行月份统计
select '11月份统计' as Subtotal,
m.empID,
sum(salaryDay) as salary,
sum(bonusDay) as bonus,
sum(deductDay) as deduct,
deptAllowance,
sum(ewCount) as ewCount
from month_11 m, tb_department d
where m.empID = d.empID
and to_char(m.service_date,'mm') = '11'
group by m.empID,deptAllowance