一段时间内的数据查询

Question

sql 2000数据库
表LogInfo字段有LogId
Title
EmpId
CreateDate     //创建时间
LastTime  //修改时间测试表LogInfo数据为
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜一','小王','2010-05-05',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜二','小王','2010-05-06','2010-05-07')
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜三','小张','2010-05-07',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜四','小王','2010-05-08',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜五','小张','2010-05-09','2010-05-11')
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜六','小王','2010-05-10','2010-05-11')现在假设要查询小王在2010-05-06到2010-05-09的数据
请问这个sql语句咋写```

Answer 1

if object_id('[LogInfo]') is not null drop table [LogInfo]
create table [LogInfo] (LogId sql_variant,Title sql_variant,EmpId sql_variant,CreateDate sql_variant,LastTime sql_variant)
go
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜一','小王','2010-05-05',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜二','小王','2010-05-06','2010-05-07')
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜三','小张','2010-05-07',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜四','小王','2010-05-08',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜五','小张','2010-05-09','2010-05-11')
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜六','小王','2010-05-10','2010-05-11')
go
select * from [LogInfo] where EmpId = '小王'  and CreateDate between '2010-05-06' and  '2010-05-09'NULL 礼拜二 小王 2010-05-06 2010-05-07
NULL 礼拜四 小王 2010-05-08 NULL

Answer 2

--？啥意思？
between '2010-05-06' and '2010-05-09'

Answer 3

create table LogInfo(LogId int identity,Title varchar(20),EmpId varchar(20),CreateDate datetime,LastTime datetime)insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜一','小王','2010-05-05',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜二','小王','2010-05-06','2010-05-07')
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜三','小张','2010-05-07',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜四','小王','2010-05-08',Null)
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜五','小张','2010-05-09','2010-05-11')
insert into LogInfo(Title,EmpId,CreateDate ,LastTime) values('礼拜六','小王','2010-05-10','2010-05-11')
select * from LogInfo where EmpId='小王'
and isnull(LastTime,CreateDate)>='2010-05-06' 
and isnull(LastTime,CreateDate)<dateadd(day,1,'2010-05-09')LogId       Title                EmpId                CreateDate              LastTime
----------- -------------------- -------------------- ----------------------- -----------------------
2           礼拜二                  小王                   2010-05-06 00:00:00.000 2010-05-07 00:00:00.000
4           礼拜四                  小王                   2010-05-08 00:00:00.000 NULL(2 行受影响)

Answer 4

create table LogInfo1(LogId int identity,Title varchar(20),EmpId varchar(20),CreateDate datetime,LastTime datetime)insert into LogInfo1(Title,EmpId,CreateDate ,LastTime) values('礼拜一','小王','2010-05-05',Null)
insert into LogInfo1(Title,EmpId,CreateDate ,LastTime) values('礼拜二','小王','2010-05-06','2010-05-07')
insert into LogInfo1(Title,EmpId,CreateDate ,LastTime) values('礼拜三','小张','2010-05-07',Null)
insert into LogInfo1(Title,EmpId,CreateDate ,LastTime) values('礼拜四','小王','2010-05-08',Null)
insert into LogInfo1(Title,EmpId,CreateDate ,LastTime) values('礼拜五','小张','2010-05-09','2010-05-11')
insert into LogInfo1(Title,EmpId,CreateDate ,LastTime) values('礼拜六','小王','2010-05-10','2010-05-11')select * from LogInfo1 where EmpId='小王' and CreateDate>='2010-05-06'
and LastTime<='2010-05-09'LogId       Title                EmpId                CreateDate              LastTime
----------- -------------------- -------------------- ----------------------- -----------------------
2           礼拜二                  小王                   2010-05-06 00:00:00.000 2010-05-07 00:00:00.000(1 行受影响)

Answer 5

select * from LogInfo where EmpId='小王' and
 CreateDate between '2010-05-06' and '2010-05-09'??

Answer 6

select * from LogInfo where EmpId='小王' 
and CreateDate between '2010-05-06' and '2010-05-09' 
and LastTime between '2010-05-06' and '2010-05-09' 
这样？？

Answer 7

请问楼上的各位那么查询小王 截止到2010-05-09的数据呢？要如何写sql?

Answer 8

select * from [LogInfo] where EmpId = '小王'  and isnull(LastTime,CreateDate)<='2010-05-09'