ORACLE中有一个表,用于记录员工签到,表中有一个date字段,name字段,但现在有个问题,就是一个员工每天的签到次数可能超过了两次(系统本身的问题),现在想要第一次签到的记录和最后一次签到的记录,中间的时间段过滤掉,请问这个SQL语句如何写,因为小北刚接触ORACLE,请教大家~~
如表中数据:
name datechen 2009-09-14 08:08:10
chen 2009-09-14 09:08:10
chen 2009-09-14 18:08:10
ling 2009-09-14 08:20:10
ling 2009-09-14 10:05:10
ling 2009-09-14 17:30:10
. .
. .
. .如何列出这样结果:
chen 2009-09-14 08:08:10 (第一条)
chen 2009-09-14 18:08:10 (最后一条)
ling 2009-09-14 08:20:10 (第一条)
ling 2009-09-14 17:30:10 (最后一条)不知道我表述的是否清楚
如表中数据:
name datechen 2009-09-14 08:08:10
chen 2009-09-14 09:08:10
chen 2009-09-14 18:08:10
ling 2009-09-14 08:20:10
ling 2009-09-14 10:05:10
ling 2009-09-14 17:30:10
. .
. .
. .如何列出这样结果:
chen 2009-09-14 08:08:10 (第一条)
chen 2009-09-14 18:08:10 (最后一条)
ling 2009-09-14 08:20:10 (第一条)
ling 2009-09-14 17:30:10 (最后一条)不知道我表述的是否清楚
union all
select name, max(date) from table group by name,trunc(date)
order by name;
select name, min(date)
from table
group by name
union
select name,max(date)
from table
group by name;
VALUES('chen',TO_DATE('2009-09-14 08:08:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('chen',TO_DATE('2009-09-14 09:08:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('chen',TO_DATE('2009-09-14 18:08:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('ling',TO_DATE('2009-09-14 08:20:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('ling',TO_DATE('2009-09-14 08:20:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('ling',TO_DATE('2009-09-14 17:30:10','YYYY-MM-DD HH24:MI:SS'));SELECT name,TO_CHAR("DATE",'YYYY-MM-DD') "日期",
TO_CHAR(MIN("DATE"),'YYYY-MM-DD HH24:MI:SS') "第一条记录",
TO_CHAR(MAX("DATE"),'YYYY-MM-DD HH24:MI:SS') "最后一条记录"
FROM test5
GROUP BY name,TO_CHAR("DATE",'YYYY-MM-DD');
VALUES('chen',TO_DATE('2009-09-14 08:08:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('chen',TO_DATE('2009-09-14 09:08:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('chen',TO_DATE('2009-09-14 18:08:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('ling',TO_DATE('2009-09-14 08:20:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('ling',TO_DATE('2009-09-14 08:20:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO test5(name, "DATE")
VALUES('ling',TO_DATE('2009-09-14 17:30:10','YYYY-MM-DD HH24:MI:SS'));SELECT name,TO_CHAR("DATE",'YYYY-MM-DD') "日期",
TO_CHAR(MIN("DATE"),'YYYY-MM-DD HH24:MI:SS') "第一条记录",
TO_CHAR(MAX("DATE"),'YYYY-MM-DD HH24:MI:SS') "最后一条记录"
FROM test5
GROUP BY name,TO_CHAR("DATE",'YYYY-MM-DD');
SELECT name,
TO_CHAR(MIN("DATE"),'YYYY-MM-DD HH24:MI:SS')||' 第一条' AS "记录"
FROM test5
GROUP BY name,TO_CHAR("DATE",'YYYY-MM-DD')
UNION
SELECT name,
TO_CHAR(MAX("DATE"),'YYYY-MM-DD HH24:MI:SS')||' 最后一条' AS "记录"
FROM test5
GROUP BY name,TO_CHAR("DATE",'YYYY-MM-DD');