SQL查询速度很慢，找不到原因

[lining0755]

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

查询速度很慢，请指正

[geniuswjt]

你自己写的这个排版自己愿意看吗？

[--小F--]

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

[--小F--]

建议：把子查询放临时表处理，你这里函数用得太多 即使加索引都失效。
不用DISTINCT 改成GROUP BY

[geniuswjt]

--试试是不是这个结果和你的一样不
select u.userid,u.name,
convert(varchar(10),c.checktime,120) as mydate,
CONVERT(varchar(5),min(c.checktime),108) as mintime,
CONVERT(varchar(5),max(c.checktime),108) as mintime
from checkinout c join userinfo u on (c.userid=u.userid) 
where c.checktime>=@tyear+@tmonth+'01' and c.checktime<dateadd(month,1,@tyear+@tmonth+'01')
group by u.userid,u.name,convert(varchar(10),c.checktime,120)
order by 1,3

[Quote=引用 1 楼 geniuswjt 的回复:]
你自己写的这个排版自己愿意看吗？
[/Quote]

[失落]

select u.userid,u.name,
convert(varchar(10),c.checktime,120) as mydate,
CONVERT(varchar(5),min(c.checktime),108) as mintime,
CONVERT(varchar(5),max(c.checktime),108) as mintime
from checkinout c join userinfo u on (c.userid=u.userid)
where c.checktime>=@tyear+@tmonth+'01' and c.checktime<dateadd(month,1,@tyear+@tmonth+'01')
group by u.userid,u.name,convert(varchar(10),c.checktime,120)
order by 1,3

看看

[叶子]

declare @tyear as int ,@tmonth as int
select @tyear = 2011,@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

/*
left(convert(varchar, c.checktime, 120), 10)
--等价于
convert(varchar(10), c.checktime, 120)
*/

[--小F--]

left(CONVERT(varchar, checktime, 120 ),10)=left(convert(varchar,c.checktime,120),10)


这个完全可以改成

convert(varchar(10),checktime,120)=convert(varchar(10),c.checktime,120)

甚至

checktime<dateadd(dd,1,c.checktime) and checktime>=c.checktime

[-晴天]

这个语句查得.........

你这儿
from checkinout as c,
userinfo as u
where year(c.checktime)=@tyear and month(c.checktime)=@tmonth

c,u 这两个表是什么关系?

如果没有确定关系,那就是全连接,既然是全连接,你上面 select 里要用 子查询干嘛,直接
select distinct
left(convert(varchar,c.checktime,120),10) as mydate,
u.userid,u.name,
substring(CONVERT(varchar, min(checktime), 120 ),12,5) as mintime,
substring(CONVERT(varchar, max(checktime), 120 ),12,5) as maxtime

不就行了!

[叶子]

substring(convert(varchar, min(checktime), 120), 12, 5)
--等价于
select convert(varchar(5), min(checktime), 108)

[lining0755]

如：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
11 2011-12-01 08:06

查询结果如下
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
张三 2011-12-01 null null
李四 2011-12-01 null null
王五 2011-12-01 null null
周六 2011-12-01 08:06 null
游七 2011-12-01 null null