表名:gk_record_new(这是一个考勤表,所有员工的考勤数据都存放在这个表里,主键为卡号和时间)表结构:
卡号:NUMBER(10)
基站:VARCHAR2(20)
时间:DATE
出入标志:VARCHAR2(1)表数据
卡号 基站 时间 出入标志
123 1# ... I
456 2# ... I
123 1# ... O
从这个表里获取当前在班上的人我写的语句是:
select * from (select * from gk_record_new where 出入标志='I' order by 时间 desc) where rownum<=4 order by 时间 desc其中“4”是所有进入的人员减去出去的差值,不过这样写好像不对请高手帮帮忙 愁死了....
卡号:NUMBER(10)
基站:VARCHAR2(20)
时间:DATE
出入标志:VARCHAR2(1)表数据
卡号 基站 时间 出入标志
123 1# ... I
456 2# ... I
123 1# ... O
从这个表里获取当前在班上的人我写的语句是:
select * from (select * from gk_record_new where 出入标志='I' order by 时间 desc) where rownum<=4 order by 时间 desc其中“4”是所有进入的人员减去出去的差值,不过这样写好像不对请高手帮帮忙 愁死了....
(select distinct id,
(select max(date) from t where t.id = id and type = 'I') din,
(select max(date) from t where t.id = id and type = 'O') dout
from t)
where din > dout and din < sysdate;
select cardno,mod(count(cardno),2) num from GK_RECORD_NEW group by cardno
) t, GK_RECORD_NEW g where t.num = 1 and g.io='I' and t.cardno = g.cardno
from (
select 卡号,基站,时间,出入标志,row_number()over(partition by 卡号 order by 时间 desc) rn from gk_record_new )
where rn=1 and 出入标志='I'
;
select * from (select * from gk_record_new where 出入标志='I' order by 时间 desc) where rownum <=4 order by 时间 desc
感觉是不对 首先查出来所有标志为进的记录 且按时间最近开始排
但是 你为了去掉出去的人直接就是取前 n 个这个不行,如果排在前边n位的 “进入记录” 前恰有 “出记录”那你不是并未排除这条, 反而将一个 进入状态的记录 因排位靠后而挤出 因为只是简单用rownum <=4
from test1 a, test1 b
where a.id = b.id
and a.jz = b.jz
and a.io_flag = 'i'
AND b.io_flag = 'o'
group by a.id, a.jz,b.io_flag
having max(a.times) - max(b.times) > 0
from test1 a, test1 b
where a.id = b.id
and a.jz = b.jz
and a.io_flag = 'i'
AND b.io_flag = 'o'
group by a.id, a.jz,b.io_flag ---这里的b.io_flag 去掉
having max(a.times) - max(b.times) > 0
select 卡号
from gk_record_new
group by 卡号
having mod(count(出入标志),2)=1
from
( select row_number() over(partition by "卡号" order by "时间" desc) rn, g.*
from gk_record_new )
where rn = 1 and "出入标志" = 'I'
from
( select row_number() over(partition by "卡号" order by "时间" desc) rn, g.*
from gk_record_new g)
where rn = 1 and "出入标志" = 'I'
看看这个可以不···