试下,也不知对不对。。 select 员工姓名,max(时间) as 最后刷卡时间 from table where 日期>=2004-07-1 and 日期<=2004-07-30 and 时间>=8.00 and 时间<=9.00
select * from 表 a where 日期 between '2004-07-01' and '2004-07-30' and 时间 between '08:00:00' and '09:00:00' and not exists(select 1 from 表 where 员工编号=a.员工编号 and 日期>a.日期 and a.时间>a.时间)
同意cwm545(罪人) 的做法 select 员工姓名,min(时间) as 最后刷卡时间 from table where 日期>=2004-07-1 and 日期<=2004-07-30 and 时间>=8.00 and 时间<=9.00
我是这样做的。可行: create table ta1(peono varchar(3),doordate datetime) insert into ta1 (peono,doordate) values ('001','2004-08-01 08:01:01') insert into ta1 (peono,doordate) values ('001','2004-08-01 08:59:08') insert into ta1 (peono,doordate) values ('001','2004-08-02 08:01:20') insert into ta1 (peono,doordate) values ('001','2004-08-02 08:54:32') insert into ta1 (peono,doordate) values ('002','2004-08-01 08:11:02') insert into ta1 (peono,doordate) values ('002','2004-08-01 08:53:08') insert into ta1 (peono,doordate) values ('002','2004-08-02 08:31:23') insert into ta1 (peono,doordate) values ('002','2004-08-02 08:55:32')select peono,max(doordate) as mydate from ta1 where doordate between '2004-08-01' and '2004-08-03' and datepart(hh,doordate) >= 8 and datepart(hh,doordate) < 9 group by peono,convert(varchar(10),doordate,120) order by peono
下面代码,经过测试: FlashDetail(EmpID,FlashDate,Amount,...)select * from FlashDetail where empid in (select Empid from FlashDetail where FlashDetail between '2004-7-1' and '2004-7-30 23:59:59:997' AND datepart(hh,FlashDetail)>=8 and datepart(hh,FlashDetail)<=9 group by empid)
谢谢大家,我也解决了. 思路与bcaianson 一致.SELECT gg.card_id, gg.card_date, gg.card_sect, min(gg.card_time) FROM [SELECT DISTINCTROW a.card_id, a.card_date, d.card_begin & d.on_duty AS card_sect, a.card_time FROM z_clock_card AS a INNER JOIN (z_employee AS b INNER JOIN (z_arrange_shift AS c INNER JOIN z_class AS d ON c.shift_id=d.class_id) ON b.empl_id=c.empl_id) ON a.card_id=b.empl_id WHERE c.r_type=0 And a.card_date Between #8/1/2004# And #8/31/2004# and (a.card_time between d.card_begin and d.on_duty ) ORDER BY 1, 2, 3, 4]. AS gg GROUP BY gg.card_id, gg.card_date, gg.card_sect;
select 员工姓名,max(时间) as 最后刷卡时间 from table where
日期>=2004-07-1 and 日期<=2004-07-30 and 时间>=8.00 and 时间<=9.00
from 表 a
where 日期 between '2004-07-01' and '2004-07-30' and 时间 between '08:00:00' and '09:00:00'
and not exists(select 1 from 表 where 员工编号=a.员工编号 and 日期>a.日期 and a.时间>a.时间)
select 员工姓名,min(时间) as 最后刷卡时间 from table where
日期>=2004-07-1 and 日期<=2004-07-30 and 时间>=8.00 and 时间<=9.00
create table ta1(peono varchar(3),doordate datetime)
insert into ta1 (peono,doordate) values ('001','2004-08-01 08:01:01')
insert into ta1 (peono,doordate) values ('001','2004-08-01 08:59:08')
insert into ta1 (peono,doordate) values ('001','2004-08-02 08:01:20')
insert into ta1 (peono,doordate) values ('001','2004-08-02 08:54:32')
insert into ta1 (peono,doordate) values ('002','2004-08-01 08:11:02')
insert into ta1 (peono,doordate) values ('002','2004-08-01 08:53:08')
insert into ta1 (peono,doordate) values ('002','2004-08-02 08:31:23')
insert into ta1 (peono,doordate) values ('002','2004-08-02 08:55:32')select peono,max(doordate) as mydate
from ta1
where doordate between '2004-08-01' and '2004-08-03' and
datepart(hh,doordate) >= 8 and datepart(hh,doordate) < 9
group by peono,convert(varchar(10),doordate,120)
order by peono
FlashDetail(EmpID,FlashDate,Amount,...)select * from FlashDetail where empid in
(select Empid from FlashDetail where FlashDetail between '2004-7-1' and '2004-7-30 23:59:59:997' AND datepart(hh,FlashDetail)>=8 and datepart(hh,FlashDetail)<=9 group by empid)
思路与bcaianson 一致.SELECT gg.card_id, gg.card_date, gg.card_sect, min(gg.card_time)
FROM [SELECT DISTINCTROW a.card_id, a.card_date, d.card_begin & d.on_duty AS card_sect, a.card_time
FROM z_clock_card AS a INNER JOIN (z_employee AS b INNER JOIN (z_arrange_shift AS c INNER JOIN z_class AS d ON c.shift_id=d.class_id) ON b.empl_id=c.empl_id) ON a.card_id=b.empl_id
WHERE c.r_type=0 And a.card_date Between #8/1/2004# And #8/31/2004# and (a.card_time between d.card_begin and d.on_duty )
ORDER BY 1, 2, 3, 4]. AS gg
GROUP BY gg.card_id, gg.card_date, gg.card_sect;