select YYMMDD,EmpNo,EmpName,PartID,PartName,PartID2,PartName2,Isnull(GO1,'00:00') GO1,Isnull(OUT1,'00:00') OUT1 from V_EmployeeDayCardData
where YYMMDD='20110512' and PartID2='LD1' order by PartID,PartID2,GO1,OUT1想要从如下数据中取一笔最能代表整个部门上下班的人的数据出来做为部门刷卡数据,真没有好的思路了,求解!!!
Isnull(OUT1,'00:00') OUT1 from V_EmployeeDayCardData
where YYMMDD=(select top 1 YYMMDD group by YYMMDD order by count(*) desc)
and PartID2='LD1' order by PartID,PartID2,GO1,OUT1
--select top 1 YYMMDD group by YYMMDD order by count(*) desc
--上面表示人最多的那天
参考:
create table tb(yymmdd datetime,go1 varchar(5))
insert into tb select '2011-06-05','15:22'
insert into tb select '2011-06-05','15:23'
insert into tb select '2011-06-05','15:15'
insert into tb select '2011-06-05','15:30'
insert into tb select '2011-06-05','15:42'
go
;with cte as(
select yymmdd,go1,abs(datediff(s,convert(varchar(11),YYMMDD)+GO1,(
select dateadd(s,avg(datediff(s,getdate(),convert(varchar(11),YYMMDD)+GO1)),getdate()) from tb
)))r from tb
)select * from cte a where not exists(select 1 from cte where r<a.r)
go
drop table tb
/*
yymmdd go1 r
----------------------- ----- -----------
2011-06-05 00:00:00.000 15:23 204(1 行受影响)
*/
select top (1) * from (
select *,r1=COUNT(*) OVER(PARTITION BY go1),r2=COUNT(*) OVER(PARTITION BY go2) from V_EmployeeDayCardData
) a order by r1+r2 desc
Isnull(OUT1,'00:00') OUT1 from V_EmployeeDayCardData
where YYMMDD=(select top 1 YYMMDD group by YYMMDD order by count(*) desc)
and PartID2='LD1' order by PartID,PartID2,GO1,OUT1