关于员工考勤的问题.
有4个表,分别如下.
员工表:
-----------------------------
员工编号 员工姓名
1 张三
2 李四
班别表:
-------------------------------------
班别编号 班别名称
-------------------------------------
1 员工班别
2 保安早班
3 保安中班
4 保安晚班
班次表:
-----------------------------------------
班别编号 班次编号 班次名称 时间
-----------------------------------------
1 1 上午上班 08:00
1 2 上午下班 12:00
1 3 下午下班 14:00
1 4 下午下班 18:00
2 1 上班 08:00
2 2 下班 16:00
3 1 上班 16:00
3 2 下班 00:00
4 1 上班 00:00
4 2 下班 08:00
考勤登记表:
-----------------------------------------
员工编号 班别编号 班次编号 登记日期 登记时间
------------------------------------------------------
1 1 1 2006/5/1 08:00
要求写一个查询,查出某一个编号的员工一个月内某一个班别的考勤.
比如: 员工1 (可能是保安,保安一个月内的班别是会变化的) 2006年1月保安早班考勤:
员工1 保安早班考勤:
----------------------------------------
登记日期 上班时间 下班时间
------------------------------------
除开上面的疑问,此题的解如下:(结果集中你想要的字段多少你可以自行在SELECT后面加入/减少)
-------------------------------------------------------------------------
if exists(select * from sysobjects where name=‘员工表‘ and xtype=‘U‘) drop table 员工表
if exists(select * from sysobjects where name=‘班别表‘ and xtype=‘U‘) drop table 班别表
if exists(select * from sysobjects where name=‘班次表‘ and xtype=‘U‘) drop table 班次表
GO
/*--生成测试用数据,为SELECT初始环境--*/
CREATE TABLE 员工表(员工编号 int,员工姓名 varchar(10))
INSERT INTO 员工表 SELECT 1,‘张三‘
UNION ALL SELECT 2,‘李四‘
CREATE TABLE 班别表(班别编号 int,班别名称 varchar(10))
INSERT INTO 班别表 SELECT 1,‘员工班别‘
UNION ALL SELECT 2,‘保安早班‘
UNION ALL SELECT 3,‘保安中班‘
UNION ALL SELECT 4,‘保安晚班‘
CREATE TABLE 班次表(班别编号 int,班次编号 int,班次名称 varchar(10),时间 varchar(20))
INSERT INTO 班次表 SELECT 1,1,‘上午上班‘,‘08:00‘
UNION ALL SELECT 1,2,‘上午下班‘,‘12:00‘
UNION ALL SELECT 1,3,‘下午下班‘,‘14:00‘
UNION ALL SELECT 1,4,‘下午下班‘,‘18:00‘
UNION ALL SELECT 2,1,‘上班‘,‘08:00‘
UNION ALL SELECT 2,2,‘下班‘,‘16:00‘
UNION ALL SELECT 3,1,‘上班‘,‘16:00‘
UNION ALL SELECT 3,2,‘下班‘,‘00:00‘
UNION ALL SELECT 4,1,‘上班‘,‘00:00‘
UNION ALL SELECT 4,2,‘下班‘,‘08:00‘
CREATE TABLE 考勤登记表(员工编号 int,班别编号 int,班次编号 int,登记日期 smalldatetime,登记时间 varchar(10))
INSERT INTO 考勤登记表 SELECT 1,1,1,‘2006/5/1‘,‘08:00‘
--select * from 员工表
--select * from 班别表
--select * from 班次表
--select * from 考勤登记表
GO
--此处为用来查询的SELECT语句
SELECT
A.员工编号,
员工姓名,
刷卡日期=Convert(varchar(10),登记日期,110),
上班时间,
下班时间
FROM
员工表 A,(
SELECT
D.*,上班时间,下班时间
FROM
班别表 B INNER JOIN (
SELECT
班别编号=MAX(班别编号),
上班时间=MAX(CASE RIGHT(班次名称,2) WHEN ‘上班‘ THEN 时间 ELSE NULL END),
下班时间=MAX(CASE RIGHT(班次名称,2) WHEN ‘下班‘ THEN 时间 ELSE NULL END)
FROM 班次表
GROUP BY 班别编号
) C ON B.班别编号=C.班别编号
INNER JOIN 考勤登记表 D ON C.班别编号=D.班别编号
) X
WHERE A.员工编号=X.员工编号
GO
--删除测试散数据
DROP TABLE 员工表,班别表,班次表,考勤登记表
/* -- 结果 --
员工编号 员工姓名 刷卡日期 上班时间 下班时间
----------- ---------- ---------- -------------------- --------------------
1 张三 05-01-2006 08:00 18:00
*/ -- 完成 --
------------------------------------------------
create table 员工表(员工编号 nvarchar(20),员工姓名 nvarchar(20))
insert 员工表 select ‘1‘,‘张三‘
union all select ‘2‘,‘李四‘
go
create table 班别表(班别编号 nvarchar(20),班别名称 nvarchar(20))
insert 班别表 select ‘1‘,‘员工班别‘
union all select ‘2‘,‘保安早班‘
union all select ‘3‘,‘保安中班‘
union all select ‘4‘ ,‘保安晚班‘
go
drop table 班次表
create table 班次表(班别编号 nvarchar(20), 班次编号 nvarchar(20), 班次名称 nvarchar(20), 时间 nvarchar(20))
insert 班次表 select ‘1‘, ‘1‘, ‘上午上班‘, ‘08:00‘
union all select ‘1‘, ‘2‘, ‘上午下班‘, ‘12:00‘
union all select ‘1‘, ‘3‘, ‘下午下班‘, ‘14:00‘
union all select ‘1‘, ‘4‘, ‘下午下班‘, ‘18:00‘
union all select ‘2‘, ‘1‘, ‘上班‘, ‘08:00‘
union all select ‘2‘, ‘2‘, ‘下班‘, ‘16:00‘
union all select ‘3‘, ‘1‘, ‘上班‘, ‘16:00‘
union all select ‘3‘, ‘2‘, ‘下班‘, ‘00:00‘
union all select ‘4‘, ‘1‘, ‘上班‘, ‘00:00‘
union all select ‘4‘, ‘2‘, ‘下班‘, ‘08:00‘
go
drop table 考勤登记表
create table 考勤登记表(员工编号 nvarchar(20), 班别编号 nvarchar(20), 班次编号 nvarchar(20), 登记日期 datetime ,登记时间 nvarchar(20))
insert 考勤登记表 select ‘1‘, ‘1‘, ‘1‘, ‘2006/5/1‘, ‘08:00‘
go
/*
要求写一个查询,查出某一个编号的员工一个月内某一个班别的考勤.
比如: 员工1 (可能是保安,保安一个月内的班别是会变化的) 2006年1月保安早班考勤:
员工1 保安早班考勤:
----------------------------------------
登记日期 上班时间 下班时间
*/
select a.员工编号,a.员工姓名,b.登记日期,c.时间 as 上班时间,d.时间 as 下班时间 from 员工表 a Left join 考勤登记表 b on a.员工编号=b.员工编号
Left join 班次表 c on b.班别编号=c.班别编号
Left join 班次表 d on b.班别编号=d.班别编号
where c.班次名称 like ‘%上班%‘ and d.班次名称 like ‘%下班%‘
---------------
员工编号 员工姓名 登记日期 上班时间 下班时间
-------------------- -------------------- ------------------------------------------------------ -------------------- --------------------
1 张三 2006-05-01 00:00:00.000 08:00 12:00
1 张三 2006-05-01 00:00:00.000 08:00 14:00
1 张三 2006-05-01 00:00:00.000 08:00 18:00
（所影响的行数为 3 行）
