-- 员工打卡表
create table EMP_SIGN_INFO
(
CARD_ID VARCHAR2(32) not null,
SIGN_DATE VARCHAR2(32),
SIGN_TIME VARCHAR2(32)
) -- 表结构-- 员工信息表
create table EMP_INFO
(
CARD_ID VARCHAR2(32) not null,
NOTEID VARCHAR2(32),
EMP_NAME VARCHAR2(32) not null,
COMPANY VARCHAR2(32) not null,
ITEM VARCHAR2(32) not null,
SEX VARCHAR2(32) not null,
STUDY VARCHAR2(32),
AGE VARCHAR2(32) not null,
WORK_YEAR VARCHAR2(32) not null,
PHONE VARCHAR2(32) not null,
SKILL VARCHAR2(32) not null,
IS_STRONG VARCHAR2(32) not null,
DEPOSIT VARCHAR2(32),
MEMO VARCHAR2(32),
EMAIL VARCHAR2(32),
RESUME VARCHAR2(256),
STATUS_TIME VARCHAR2(32),
STATUS VARCHAR2(32) not null,
ROLE_ID VARCHAR2(32),
SIB_PHONE VARCHAR2(32),
SIB_NAME VARCHAR2(32),
PIC_URL VARCHAR2(256),
BEGINJOB_DATE VARCHAR2(32),
ENDJOB_DATE VARCHAR2(32)
)要求,查询出当天所有迟到人员的信息, 也就是在早上6:00到9:30没有打卡的人员信息,要显示人员工号,打卡时间,昨日下班时间!
谢谢
create table EMP_SIGN_INFO
(
CARD_ID VARCHAR2(32) not null,
SIGN_DATE VARCHAR2(32),
SIGN_TIME VARCHAR2(32)
) -- 表结构-- 员工信息表
create table EMP_INFO
(
CARD_ID VARCHAR2(32) not null,
NOTEID VARCHAR2(32),
EMP_NAME VARCHAR2(32) not null,
COMPANY VARCHAR2(32) not null,
ITEM VARCHAR2(32) not null,
SEX VARCHAR2(32) not null,
STUDY VARCHAR2(32),
AGE VARCHAR2(32) not null,
WORK_YEAR VARCHAR2(32) not null,
PHONE VARCHAR2(32) not null,
SKILL VARCHAR2(32) not null,
IS_STRONG VARCHAR2(32) not null,
DEPOSIT VARCHAR2(32),
MEMO VARCHAR2(32),
EMAIL VARCHAR2(32),
RESUME VARCHAR2(256),
STATUS_TIME VARCHAR2(32),
STATUS VARCHAR2(32) not null,
ROLE_ID VARCHAR2(32),
SIB_PHONE VARCHAR2(32),
SIB_NAME VARCHAR2(32),
PIC_URL VARCHAR2(256),
BEGINJOB_DATE VARCHAR2(32),
ENDJOB_DATE VARCHAR2(32)
)要求,查询出当天所有迟到人员的信息, 也就是在早上6:00到9:30没有打卡的人员信息,要显示人员工号,打卡时间,昨日下班时间!
谢谢
from EMP_SIGN_INFO a, (select card_id from EMP_SIGN_INFO where SIGN_DATE= trunc(sysdate) and time between '6:00' and '9:30') b,
(select card_id,trim(max(time)) lastday_time from EMP_SIGN_INFO where SIGN_DATE= trunc(sysdate-1))c
where a.card_id=b.card_id(+)
AND a.card_id=c.card_id(+)
AND a.SIGN_DATE= trunc(sysdate)
AND b.card_id is null
对于特殊的应用,时间用字符串类型还是有一定的好处的,像我们现在做的系统,里面的时间基本上都是用的字符串类型,在程序处理的时候比较方便。而做成DATE类型的话,可能在程序中就得先转换后再处理了……视具体应用情况……