如何实现快速查询
现在在做一个打卡机的查询程序,有两个表,一个是人员信息表,一个是打卡信息表(一天要打两次卡,要记录两条打开信息),现在需要将两条打卡信息记录到一行,并将所有人员打卡信息进行统一(其中包括未打卡人员),12点之前的打卡信息算为上午打卡时间,12点之后打卡算为下午打卡时间。
表一,人员信息表(卡号,部门,姓名)
create table empInfo
(cardno varchar(10),dept varchar(20),name varchar(10))
insert into empInfo
select '1000000001','部门一','张三'
union all select '1000000002','部门一','李四'
union all select '1000000004','部门二','王五'
union all select '1000000010','部门四','赵六'
create table daka
(PeoNo varchar(10),KqDate varchar(00),KqTime varchar(10))
insert into daka
select '1000000001','2013-10-08','07:45'
union all select '1000000002','2013-10-08','07:53'
union all select '1000000004','2013-10-08','07:45'
union all select '1000000002','2013-10-08','16:53'
union all select '1000000004','2013-10-08','17:02'
卡号 姓名 部门 日期 上午打卡时间 下午打卡时间
1000000001 张三 部门一 2013-10-08 07:45
1000000002 李四 部门一 2013-10-08 07:53 16:53
1000000004 王五 部门二 2013-10-08 07:45 17:02
1000000010 赵六 部门四 2013-10-08
create table empInfo
(cardno varchar(10),dept varchar(20),name varchar(10))
insert into empInfo
select '1000000001','部门一','张三'
union all select '1000000002','部门一','李四'
union all select '1000000004','部门二','王五'
union all select '1000000010','部门四','赵六'
create table daka
(PeoNo varchar(10),KqDate varchar(10),KqTime varchar(10))
insert into daka
select '1000000001','2013-10-08','07:45'
union all select '1000000002','2013-10-08','07:53'
union all select '1000000004','2013-10-08','07:45'
union all select '1000000002','2013-10-08','16:53'
union all select '1000000004','2013-10-08','17:02'
SELECT a.*,b.KqDate,MIN(KqTime) AS 上午打卡时间,MAX(KqTime) AS 下午打卡时间 FROM empInfo a LEFT JOIN daka b ON a.cardno=b.PeoNo GROUP BY a.cardno,a.dept,a.NAME,b.KqDate
DROP TABLE empInfo,daka
/*cardno dept name KqDate 上午打卡时间 下午打卡时间
---------- -------------------- ---------- ---------- ---------- ----------
1000000001 部门一 张三 2013-10-08 07:45 07:45
1000000002 部门一 李四 2013-10-08 07:53 16:53
1000000004 部门二 王五 2013-10-08 07:45 17:02
1000000010 部门四 赵六 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了 Null 值。*/
--create table empInfo
--(cardno varchar(10),dept varchar(20),name varchar(10))
--insert into empInfo
--select '1000000001','部门一','张三'
--union all select '1000000002','部门一','李四'
--union all select '1000000004','部门二','王五'
--union all select '1000000010','部门四','赵六'
--create table daka
--(PeoNo varchar(10),KqDate varchar(100),KqTime varchar(10))
--insert into daka
--select '1000000001','2013-10-08','07:45'
--union all select '1000000002','2013-10-08','07:53'
--union all select '1000000004','2013-10-08','07:45'
--union all select '1000000002','2013-10-08','16:53'
--union all select '1000000004','2013-10-08','17:02'
--union all select '1000000002','2013-10-09','07:53'
--union all select '1000000004','2013-10-09','07:45'
--union all select '1000000002','2013-10-09','16:53'
--union all select '1000000004','2013-10-09','17:02'
SELECT DISTINCT e.*,MAX(CASE WHEN d.kqtime<='12:00' THEN kqtime END) 上午打卡时间 ,MAX(CASE WHEN d.kqtime>'12:00' THEN kqtime END) 下午打卡时间
FROM (
SELECT *
FROM (select DISTINCT kqdate FROM daka) d cross JOIN (SELECT DISTINCT name,cardno,dept FROM empInfo) b)e left JOIN daka d ON e.cardno=d.peono
GROUP BY e.kqdate,e.NAME,e.cardno,e.dept
ORDER BY e.kqdate,e.name
/*
kqdate name cardno dept 上午打卡时间 下午打卡时间
---------------------------------------------------------------------------------------------------- ---------- ---------- -------------------- ---------- ----------
2013-10-08 李四 1000000002 部门一 07:53 16:53
2013-10-08 王五 1000000004 部门二 07:45 17:02
2013-10-08 张三 1000000001 部门一 07:45 NULL
2013-10-08 赵六 1000000010 部门四 NULL NULL
2013-10-09 李四 1000000002 部门一 07:53 16:53
2013-10-09 王五 1000000004 部门二 07:45 17:02
2013-10-09 张三 1000000001 部门一 07:45 NULL
2013-10-09 赵六 1000000010 部门四 NULL NULL
*/