declare @tyear as int,@tmonth as int
set @tyear=2011
set @tmonth=10
select distinct left(convert(varchar,c.checktime,120),10) as mydate,u.userid,u.name,
(select substring(CONVERT(varchar, min(checktime), 120 ),12,5)
from checkinout
where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as mintime,
(select substring(CONVERT(varchar, max(checktime), 120 ),12,5)
from checkinout
where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as maxtime
from checkinout as c,userinfo as u where year(c.checktime)=@tyear and month(c.checktime)=@tmonth order by u.userid,mydate
set @tyear=2011
set @tmonth=10
select distinct left(convert(varchar,c.checktime,120),10) as mydate,u.userid,u.name,
(select substring(CONVERT(varchar, min(checktime), 120 ),12,5)
from checkinout
where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as mintime,
(select substring(CONVERT(varchar, max(checktime), 120 ),12,5)
from checkinout
where left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10) and userid=u.userid) as maxtime
from checkinout as c,userinfo as u where year(c.checktime)=@tyear and month(c.checktime)=@tmonth order by u.userid,mydate
substring(CONVERT(varchar, min(checktime), 120 ),12,5)
换成
convert(varchar(5),min(checktime),108)
把
left(CONVERT(varchar, checktime, 120 ),10)
换成
convert(varchar(10),checktime,120)
select distinct convert(varchar(10),c.checktime,120) as mydate,u.userid,u.name,
(select substring(CONVERT(varchar, min(checktime), 120 ),12,5) from checkinout where CONVERT(varchar(10), checktime, 120 )= CONVERT(varchar(10), c.checktime, 120 ) and userid=u.userid)
from checkinout as c,userinfo as u
where year(c.checktime)=@tyear and month(c.checktime)=@tmonth
order by u.userid,mydate
逻辑也太混乱了吧,能把人看晕了,醒了再晕一次。
把
substring(CONVERT(varchar, min(checktime), 120 ),12,5)
换成
convert(varchar(5),min(checktime),108)
把
left(CONVERT(varchar, checktime, 120 ),10)
换成
convert(varchar(10),checktime,120)
函数更改以后速度还是很慢
“SELECT u.name, format(c.checktime,"YYYY-MM-DD") AS mydate, format(min(c.checktime),"hh:mm") AS intime, format(max(c.checktime),"hh:mm") AS outtime FROM checkinout AS c, userinfo AS u WHERE c.userid=u.userid GROUP BY format(c.checktime,"YYYY-MM-DD"), u.name;”
这样只能查询出在checkinout表有记录的数据,如果当天员工请假或者外出都没有打卡就会没有记录,我想把没有打卡的记录也显示出来,用“NULL”显示,查询语句该怎么写?我想要的效果就是能显示所有员工每天的出勤情况,没有记录的就以NULL显示
如:checkinout userinfo
userid checktime userid name
14 2011-12-02 8:30 14 张三
14 2011-12-02 17:50 13 李四
13 2011-12-02 8:25 12 王五
12 2011-12-02 8:10 11 周六
12 2011-12-02 17:57 10 游七
11 2011-12-02 18:00 查询结果如下
name mydate intime outtime
张三 2011-12-02 08:30 17:50
李四 2011-12-02 08:25 null
王五 2011-12-02 08:10 17:57
周六 2011-12-02 null 18:00
游七 2011-12-02 null null
format(c.checktime,"YYYY-MM-DD") AS mydate,
format(min(c.checktime),"hh:mm") AS intime,
format(max(c.checktime),"hh:mm") AS outtime
FROM checkinout AS c,
left join userinfo AS u on c.userid=u.userid
GROUP BY u.name, format(c.checktime,"YYYY-MM-DD") AS mydate
format(c.checktime,"YYYY-MM-DD") AS mydate,
format(min(c.checktime),"hh:mm") AS intime,
format(max(c.checktime),"hh:mm") AS outtime
FROM userinfo AS u,
left join checkinout AS c on c.userid = u.userid
GROUP BY u.name, format(c.checktime,"YYYY-MM-DD") AS mydate
直接使用 year() month() [day()可选] 函数 得到年月 [日]就可以 进行过滤 因为你没有要求 结果在一个特定时间点之前或之后
至于想要显示没有打卡的同事,可以用左外连接或者叫右外连接 不过不是显示NULL 而是显示空白 不知道你能否接受当然说这些的前提是access 有year month 函数 如果没有 就当我没说
SQL:
一、
select a.dt,a.name,max(convert(varchar(5),b.checktime,108))maxtime,min(convert(varchar(5),b.checktime,108))mintime
from (
select distinct convert(varchar(10),a.checktime,120)dt,b.userid,b.name from checkinout a,userinfo b
)a left join checkinout b on a.dt=convert(varchar(10),b.checktime,120) and a.userid=b.userid
group by a.dt,a.name
order by a.dt descACCESS:
一、
select a.dt,a.name,format(min(b.checktime),"hh:mm") AS intime, format(max(b.checktime),"hh:mm") AS outtime
from (
select distinct format(a.checktime,"YYYY-MM-DD") AS dt,b.userid,b.name from checkinout a,userinfo b
)a left join checkinout b on a.dt=format(b.checktime,"YYYY-MM-DD") and a.userid=b.userid
group by a.dt,a.name
order by a.dt desc