我要制作考勤统计系统,基本需求如下:
我查询出12月分所有员工在07:00-08:00和12:00-13:00和17:00-18:00的所有打卡记录
select * from noteinf where convert(char(7),t_notetime,120)='2008-12'and
(convert(char(10),t_notetime,108) between '07:00' and '08:00'
or convert(char(10),t_notetime,108) between '12:00' and '13:00'
or convert(char(10),t_notetime,108) between '17:00' and '18:00')
但是在每个时间段,比如07:00-08:00可能同一个人有两条或两条以上考勤记录,那么我只查询出记录最早的那一条,请问这条查询语句应该怎么写?
我查询出12月分所有员工在07:00-08:00和12:00-13:00和17:00-18:00的所有打卡记录
select * from noteinf where convert(char(7),t_notetime,120)='2008-12'and
(convert(char(10),t_notetime,108) between '07:00' and '08:00'
or convert(char(10),t_notetime,108) between '12:00' and '13:00'
or convert(char(10),t_notetime,108) between '17:00' and '18:00')
但是在每个时间段,比如07:00-08:00可能同一个人有两条或两条以上考勤记录,那么我只查询出记录最早的那一条,请问这条查询语句应该怎么写?
解决方案 »
- 调用dll时如何定义回调函数
- 电脑com口与第三方设备的com不一致怎么办?比如电脑com口名字是com9,而设备用的是com1,怎么办?
- 请问如何在vs2005中做移动web项目
- DataGrid 的插入行(列)的方法??
- 求助:vs2005新建网站没有ASP.net模板
- powershell 访问窗口控件属性 线程间操作无效
- C#winform 线程启动窗体 未响应问题
- 关于encoding.getEncoding("gb2312").getstring(bytes),一句话的问题
- 谁可以告诉我,怎么2000的DOS下怎么输入中文
- 如果我把一个图标添加到资源里面,那么我在程序中怎么用它?
- 方法"XX" 沒有任何多載使用一個引數
- xp下[shell32]读取mp3文件信息的tag
case convert(char(10),t_notetime,108)between '07:00' and '08:00' then 1
case convert(char(10),t_notetime,108)between '12:00' and '13:00' then 2
newtime
form ...(这块不变)
group by newtimenewtime是case 结果的别名以上语法狗屁不通,LZ自己调试哦。
group by * where convert(char(10),t_notetime,108) between '07:00' and '08:00'
union
select min(t_notetime),* from noteinf where convert(char(7),t_notetime,120)='2008-12'
group by * where convert(char(10),t_notetime,108) between '12:00' and '13:00'
union
select min(t_notetime),* from noteinf where convert(char(7),t_notetime,120)='2008-12'
group by * where convert(char(10),t_notetime,108) between '17:00' and '18:00')
select min(t_notetime) t_notetime from noteinf where
convert(char(10),t_notetime,108) between '07:00' and '08:00' ) a,
(select min(t_notetime) t_notetime from noteinf where
convert(char(10),t_notetime,108) between '12:00' and '13:00' ) b,
(select min(t_notetime) t_notetime from noteinf where
convert(char(10),t_notetime,108) between '17:00' and '18:00') c
where
convert(char(7),a.t_notetime,120)='2008-12' and
convert(char(7),b.t_notetime,120)='2008-12' and
convert(char(7),c.t_notetime,120)='2008-12'
select 用户ID,min(t_notetime)
from noteinf where convert(char(7),t_notetime,120)='2008-12'and
(convert(char(10),t_notetime,108) between '07:00' and '08:00'
)
group by 用户ID,t_notetime
order by 用户ID,t_notetimeunion select 用户ID,min(t_notetime)
from noteinf where convert(char(7),t_notetime,120)='2008-12'and
(convert(char(10),t_notetime,108) between '12:00' and '13:00'
)
group by 用户ID,t_notetime
order by 用户ID,t_notetimeunionselect 用户ID,min(t_notetime)
from noteinf where convert(char(7),t_notetime,120)='2008-12'and
(convert(char(10),t_notetime,108) between '17:00' and '18:00'
)
group by 用户ID,t_notetime
order by 用户ID,t_notetime
select *,row_number()over(partition by name,d,t order by t_notetime) rn
from(
select name,t_notetime,d=convert(varchar(10),t_notetime,120),t=datepart(hh,t_notetime)
from noteinf
where t_notetime between '2008-12-01' and '2009-10-01'
and datepart(hh,t_notetime) in(7,12,17))a)b where rn=1
and convert(char(5),t_notetime,108) between '07:00' and '08:00'
group by [人员姓名]
union
select min(t_notetime),[人员姓名] from noteinf where (char(7),t_notetime,120)='2009-12'
and convert(char(5),t_notetime,108) between '12:00' and '13:00'
group by [人员姓名]
union
select min(t_notetime),[人员姓名] from noteinf where (char(7),t_notetime,120)='2009-12'
and convert(char(5),t_notetime,108) between '17:00' and '18:00'
group by [人员姓名]没测试你测测再说~