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)[email protected] and month(c.checktime)[email protected] order by u.userid,mydate
查询速度很慢，请指正
------解决方案--------------------
你自己写的这个排版自己愿意看吗？
------解决方案--------------------
- SQL code
declare @tyear as int,@tmonth as intset @tyear=2011set @tmonth=10select 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 maxtimefrom checkinout as c,userinfo as u where year(c.checktime)[email protected] and month(c.checktime)[email protected] order by u.userid,mydate
------解决方案--------------------
建议：把子查询放临时表处理，你这里函数用得太多 即使加索引都失效。
不用DISTINCT 改成GROUP BY
------解决方案--------------------
- SQL code
--试试是不是这个结果和你的一样不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 mintimefrom checkinout c join userinfo u on (c.userid=u.userid) where c.checktime>[email protected][email protected]+'01' and c.checktime<dateadd(month,1,@[email protected]+'01')group by u.userid,u.name,convert(varchar(10),c.checktime,120)order by 1,3
------解决方案--------------------
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>[email protected][email protected]+'01' and c.checktime<dateadd(month,1,@[email protected]+'01')
group by u.userid,u.name,convert(varchar(10),c.checktime,120)
order by 1,3
看看
------解决方案--------------------
- SQL code
declare @tyear as int ,@tmonth as intselect @tyear = 2011,@tmonth = 10select 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 maxtimefrom checkinout as c ,userinfo as uwhere year(c.checktime) = @tyear and month(c.checktime) = @tmonthorder by u.userid ,mydate/*left(convert(varchar, c.checktime, 120), 10)--等价于convert(varchar(10), c.checktime, 120)*/