查询结果速度很慢，要2分钟才能出结果，求解

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

改成inner join

晕了 没有发现可以加索引的字段了


函数是利用不了索引的。

把
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)

SQL code

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)


函数更改以后速度还是很慢

在考勤系统中，access数据库有checkinout表和userinfo表，chenkinout表有userid和checktime字段，userinfo有userid和name字段，在access查询中:
“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