应用举例
eg1:
create table t(日期 char(8),请假人数 int)
insert t select '20031001',3
Union all select '20031003',2
Union all select '20031004',1
Union all select '30031031',5
要列出2003年10月每一天的请假人数,若没有,以0表示。
Select convert(char(8),dateadd(day,id,'20031001'),112),IsNull(t.请假人数,0) from
(Select top 31 (select sum(1) from sysobjects where name<= a.name)-1 as id from sysobjects a) bb
left join t on convert(char(8),dateadd(day,id,'20031001'),112) = t.日期
select * from data a where time between @starttime and @endtime
and time=(select max(time) from data where datediff(hh,time,a.time)=0 and
time between @startime and @endtime and userid=a.userid)
单用户数据搜索可以做到,多用户数据数据搜索怎么做。
select * from data a where time between @starttime and @endtime and time=(select max(time) from data where datediff(hh,time,a.time)=0 and time between @startime and @endtime)
如果用户号字段为userid,按用户号分类 (group by userid)怎么做?????
SELECT * FROM tblName WHERE 时间列 BETWEEN (COVNERT(CONVERT(VARCHAR(10),时间列,121) AS DATETIME , dayadd(h,1,CONVERT(CONVERT(VARCHAR(10),时间列,121) as datetime)
比如我的表名为data,他的时间列为time,是不是下面的语句?
select * from data a where time between(@starttime and @endtime) and time=(select max(time) from data where datediff(hh,time,a.time)=0 and time between(@startime and @endtime))
select * from 表 tem where 时间列 between @你的开始时间 and @结束时间 and 时间列=(select min(时间列) from 表 where datediff(hh,时间列,tem.时间列)=0 and 时间列 between @你的开始时间 and @结束时间)
或:
select * from 表 tem where 时间列=(select max(时间列) from 表 where datediff(hh,时间列,tem.时间列)=0 and 时间列 between @你的开始时间 and @结束时间)
select * from 表 tem where 时间列 between @你的开始时间 and 结束时间 and 时间列=(select max(时间列) from 表 where datediff(hh,时间列,tem.时间列)=0 and 时间列 between @你的开始时间 and 结束时间)