日期:2014-05-18  浏览次数:20646 次

SQL查询速度很慢,要怎么才能优化,或者有更好的方法达到查询效果
declare @tyear as int,@tmonth as int
set @tyear=2011
set @tmonth=10
select
 distinct convert(varchar(10), c.checktime, 120) as mydate,
 u.userid,u.name,
 (select
  convert(varchar(5), min(checktime), 108) 
  from
  checkinout 
  where
  convert(varchar(10), checktime, 120)=convert(varchar(10), c.checktime, 120) 
  and
  userid=u.userid) as mintime,
 (select
  convert(varchar(5), max(checktime), 108) 
  from
  checkinout where convert(varchar(10), checktime, 120)=convert(varchar(10), c.checktime, 120)
  and userid=u.userid) as maxtime
from
  checkinout as c,userinfo as u 
where
  c.checktime between '2011-11-01' and '2011-11-30'

order by
  mydate


表的关系:
userinfo:
NAME USERID
刘一 1
周二 2
李三 3
徐四 4
王五 5
邹六 6
黄七 7
林八 8
易九 9
蔺十 10


checkinout:
CHECKTIME USERID
2011-11-1 8:25 1
2011-11-1 17:33 1
2011-11-1 8:31 2
2011-11-1 17:42 2
2011-11-1 8:26 4
2011-11-1 19:50 4
2011-11-1 8:25 5
2011-11-1 17:32 5
2011-11-1 8:26 7
2011-11-1 17:32 7

2011-11-2 13:57 3
2011-11-2 17:48 3
2011-11-2 8:26 4
2011-11-2 18:51 4
2011-11-2 8:26 5
2011-11-2 17:30 5
2011-11-2 8:27 6
2011-11-2 18:34 6
2011-11-2 8:26 7
2011-11-2 19:49 7
2011-11-2 8:28 8


2011-11-3 8:27 3
2011-11-3 18:47 3
2011-11-3 8:10 4
2011-11-3 17:36 4
2011-11-3 8:28 7
2011-11-3 17:38 7
2011-11-3 8:24 8
2011-11-3 17:31 8


查询结果如下:
MYDATE NAME MINTIME MAXTIME
2011-11-1 刘一 8:25 17:33
2011-11-1 周二 8:31 17:42
2011-11-1 李三 NULL NULL
2011-11-1 徐四 8:26 19:50
2011-11-1 王五 8:25 17:32
2011-11-1 邹六 NULL NULL
2011-11-1 黄七 8:26 17:32
2011-11-1 林八 NULL NULL
2011-11-1 易九 NULL NULL
2011-11-1 蔺十 NULL NULL

2011-11-2 刘一 NULL NULL
2011-11-2 周二 NULL NULL
2011-11-2 李三 13:57 17:48
2011-11-2 徐四 8:26 18:51
2011-11-2 王五 8:26 17:30
2011-11-2 邹六 8:27 18:34
2011-11-2 黄七 8:26 19:49
2011-11-2 林八 8:28 8:28
2011-11-2 易九 NULL NULL
2011-11-2 蔺十 NULL NULL

2011-11-3 刘一 NULL NULL
2011-11-3 周二 NULL NULL
2011-11-3 李三 8:27 18:47
2011-11-3 徐四 8:10 17:36
2011-11-3 王五 NULL NULL
2011-11-3 邹六 NULL NULL
2011-11-3 黄七 8:28 17:38
2011-11-3 林八 8:24 17:31
2011-11-3 易九 NULL NULL
2011-11-3 蔺十 NULL NULL


------解决方案--------------------
SQL code
declare @tyear as int,@tmonth as int
set @tyear=2011
set @tmonth=10
select
 mydate,
 u.userid,u.name,
  mintime,
 maxtime 
from
userinfo as u ,
    (
    select
         userid, 
        convert(varchar(10), c.checktime, 120) as mydate,
        convert(varchar(5), min(checktime), 108)   mintime,
        convert(varchar(5), max(checktime), 108)  maxtime
      from
          checkinout 
    where  
        checktime between '2011-11-01' and '2011-11-30'
    GROUP BY userid
    )T

where T.userid=u.userid

order by  mydate

------解决方案--------------------
从跟底下优化吧。
------解决方案--------------------
探讨
SQL code

declare @tyear as int,@tmonth as int
set @tyear=2011
set @tmonth=10
select
mydate,
u.userid,u.name,
mintime,
maxtime
from
userinfo as u ,
(
select
userid……

测试文档5,该如何处理