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
);
我想查出 tb_salary 表中所有字段 与empid =tb_department=tb_Extrawork=tb_Attendance中attdate与EwDate 在同一个月中的数据
----------------
EmpID Salary bonus deduct EwDate AttDate ConutAtt deptAllowance EwConut
我发现我对多表连接查询并不懂还希望你们给我点学习建议,谢谢
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
);
我想查出 tb_salary 表中所有字段 与empid =tb_department=tb_Extrawork=tb_Attendance中attdate与EwDate 在同一个月中的数据
----------------
EmpID Salary bonus deduct EwDate AttDate ConutAtt deptAllowance EwConut
我发现我对多表连接查询并不懂还希望你们给我点学习建议,谢谢
select ts.EmpID,ts.Salary,ts.bonus,ts.deduct,
te.ewdate,te.EwConut,
ta.attdate,ta.ConutAtt
from tb_salary ts,
tb_Extrawork te,
tb_Attendance ta
where ts.empid = te.empid
and te.empid = ta.empid
and to_char(te.EwDate,'MM') = to_char(ta.AttDate,'MM')
order by te.EwDate;要得到deptAllowance信息需要通过employee表的depID去连tb_department.
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