有三张表做联合查询,想把某部门一天之内重复打卡的记录只按打一次处理(目的:为了统计某天出勤人数,如果每刷一次卡就算出勤,哪么出勤人数一定会增加,造成统计不真实)表一(部门表):mng_departmentid department表二(人员表):user_fileid user_name depart_id userid_num表三(刷卡记录表):event_recorderid event_date user_file表一、表二关系:mng_department.id=user_file.depart_id表二、表三关系:user_file.id=event_recorder.user_file现有语句:SELECT [user_file].[user_name],[user_file].[userid_num],
[event_recorder].[event_date]
from user_file inner join [event_recorder]
on [user_file].[id]=[event_recorder].[user_file]
where depart_id=(select [id] from [mng_department] where [department]='后勤保障部') and [event_date]>='2008-06-10 0:00:00' and [event_date]<='2008-06-10 23:59:59'
查询结果如下:张 涛 05169 2008-06-10 06:57:09.110
刘雪云 05259 2008-06-10 07:06:43.343
吴玲云 05258 2008-06-10 07:06:45.267
吴苗苗 05261 2008-06-10 07:11:22.610
吴苗苗 05261 2008-06-10 07:12:13.407
吴苗苗 05261 2008-06-10 07:12:14.407可以看出,05261这个用户连续三次打卡,但是我想只让05261显示一次,请教高人如何在以上语句中加以改造。
[event_recorder].[event_date]
from user_file inner join [event_recorder]
on [user_file].[id]=[event_recorder].[user_file]
where depart_id=(select [id] from [mng_department] where [department]='后勤保障部') and [event_date]>='2008-06-10 0:00:00' and [event_date]<='2008-06-10 23:59:59'
查询结果如下:张 涛 05169 2008-06-10 06:57:09.110
刘雪云 05259 2008-06-10 07:06:43.343
吴玲云 05258 2008-06-10 07:06:45.267
吴苗苗 05261 2008-06-10 07:11:22.610
吴苗苗 05261 2008-06-10 07:12:13.407
吴苗苗 05261 2008-06-10 07:12:14.407可以看出,05261这个用户连续三次打卡,但是我想只让05261显示一次,请教高人如何在以上语句中加以改造。
select [user_file].[user_name], min([user_file].[userid_num]), min([event_recorder].[event_date])
from user_file inner join [event_recorder]
on [user_file].[id]=[event_recorder].[user_file]
where depart_id=(select [id] from [mng_department] where [department]='后勤保障部')
and [event_date]>='2008-06-10 0:00:00' and [event_date] <='2008-06-10 23:59:59'
group by [user_file].[user_name]
select ObjectId,RANK() OVER(PARTITION BY ObjectId Order BY VisitTime DESC) RANK from Website_ViewCount
where UserId=15 and ChannelId=7 group by ObjectId,VisitTime
) select * from NewTable where Rank<2