[已解决问题] sql server 分组查询
提问时间: 2008-06-30 12:07
悬赏分：20 浏览:259 次
求救:
我有个考勤表 snm_checkwork
字段有 id,checktime(上班时间),leavetime(下班时间),status(状态),uid(FK_考勤人编号)
我想查询出按考勤的人uid分组显示内容,即相同人的考勤纪录显示在一起
改怎么写 tsql语句啊?!
我有个考勤表 snm_checkwork
字段有 id,checktime(上班时间),leavetime(下班时间),status(状态),uid(FK_考勤人编号)
我想查询出按考勤的人uid分组显示内容,即相同人的考勤纪录显示在一起
改怎么写 tsql语句啊?!
问题补充：表中的所有字段都显示出来
只是显示的结果是将把相同uid的字段排列在一起
最佳答案
select * from snm_checkwork order by uid
select * from snm_checkwork order by uid desc
2008/6/30 12:57:08
回答者：风海迷沙
其它回答(4)
|
没有明白你的需求，你的查询结果都有那些字段。 是不是Group By K_考勤人编号 一下就可以了 具体也不清楚你要完成什么功能,象这种数据分析的处理最好能有存储过程来实现,我下面有个计算考勤加班计算的存储过程,你可以参考以下: CREATE proc aSP_OTCL_ADD as Begin declare @term datetime , @begindate datetime, @enddate datetime select @term=Term,@begindate=Begindate,@enddate=enddate from skyattendprocess select Badge,sum(Num) Num into #OTCL_BAL from ashift_details where term between @begindate and @enddate and TMWG='RTTM' group by badge --周六加班 select Badge,sum(Num) Num into #OT2_BAL from ashift_details where term between @begindate and @enddate and TMWG='OTTM' and TMWGSUB in ('OT2U') and datepart(weekday,term)=7 and Num<>0 group by badge --非周六加班 select Badge,sum(Num) Num into #OT_BAL from ashift_details where term between @begindate and @enddate and TMWG='OTTM' and not (TMWGSUB in ('OT2U') and datepart(weekday,term)=7 ) and Num<>0 group by badge insert aOTCL_BaL(Term,Badge) select @term,badge from #OTCL_BAL a where not exists(select 1 from aOTCL_BaL b where a.badge=b.badge and datediff(month,b.term,@term)=0 ) insert aOTCL_BaL(Term,Badge) select @term,badge from #OT2_BAL a where not exists(select 1 from aOTCL_BaL b where a.badge=b.badge and datediff(month,b.term,@term)=0 ) insert aOTCL_BaL(Term,Badge) select @term,badge from #OT_BAL a where not exists(select 1 from aOTCL_BaL b where a.badge=b.badge and datediff(month,b.term,@term)=0 ) update a set a.RThis =0 from aOTCL_Bal a where datediff(month,term,@term)=0 Update a set a.RThis=b.Num from aOTCL_Bal a, #OTCL_BaL b where a.badge=b.badge and datediff(month,a.term,@term)=0 --and datediff(month,b.term,@term)=0 Update a set a.Rtotal=a.Rlast+a.RThis ,a.RBal =a.Rlast+a.RThis-a.RUsed from aOTCL_Bal a where datediff(month,a.term,@term)=0 --加班(周六加班) Update a set a.O2This =0 from aOTCL_Bal a where datediff(month,term,@term)=0 Update a set a.O2This=b.Num from aOTCL_Bal a, #OT2_BaL b where a.bad 呵呵，如果只是要显示到一起，直接加order by select * from snm_checkwork order by uid |
评论
|
3个月前 照清 : --怎么丢掉了一部分呢?郁闷ing --接上面的where a.bad where a.badge=b.badge and datediff(month,a.term,@term)=0 Update a set a.O2total=a.O2last+a.O2This from aOTCL_Bal a where datediff(month,a.term,@term)=0 --加班(非周六加班) Update a set a.OTotal =0 from aOTCL_Bal a where datediff(month,term,@term)=0 Update a set a.OTotal=b.Num from aOTCL_Bal a, #OT_BaL b where a.badge=b.badge and datediff(month,a.term,@term)=0 drop table #OTCL_BaL drop table #OT2_BaL drop table #OT_BaL End GO |
|
3个月前 yeyang : 谢谢大家的帮忙 原来是我把分组显示和排序搞混了 这个问题只要排序就可以了 我还以为要分组呢! |