SELECT @查询时间,*
FROM 人员信息表 a
WHERE 时间=
(
SELECT TOP 1 时间
FROM 人员信息表
WHERE 人员编号=a.人员编号
AND 时间<@查询时间
ORDER BY 时间 DESC
)
FROM 人员信息表 a
WHERE 时间=
(
SELECT TOP 1 时间
FROM 人员信息表
WHERE 人员编号=a.人员编号
AND 时间<@查询时间
ORDER BY 时间 DESC
)
解决方案 »
- 实时数据,按分钟、小时、天来查询,sql语句如何写?
- 数据库怎么添加一条新纪录 并且 只在主键上添加一个值,请问有谁知道吗?
- sql函数如何改变传入参数的值
- 一个超级难,超级纠结的查询,求高高高高手来搞定
- 怎么马上知道谁访问了我的SQL,有什么工具或用什么命令呢
- 哪种索引好?
- 一个关于SQL触发器的问题!!很急!!谢谢!!
- sqlserver 标准版 = ????Money????????????????
- 同一条SQL语句,单独运行正确,放到函数里不正确,有哪位高手知道的话,请指教我一下吧
- 求结果集
- SQL Server 2005与vista兼容吗?
- 两个数据库中Table数据差异传递,用哪个方法好呢?
set @查询时间='2008-12-31'
SELECT @查询时间 as 查询时间,*
FROM tb a
WHERE 时间=
(
SELECT max(时间)
FROM tb
WHERE 人员编号=a.人员编号
)
(flag varchar(4), UpdTime datetime,Sno varchar(10),Name varchar(10),sex varchar(10),dept varchar(10),
sts varchar(10)
)
INSERT INTO @t
SELECT
'新增' , '2008-9-1 12:01' , '001' , '李大钊' , '男' , '业务部' , '在职'
UNION ALL SELEcT
'新增' , '2008-9-1 12:05' , '002','王美丽' , '女' , '财务部' , '在职'
UNION ALL SELEcT
'修改' , '2008-10-12 08:21' , '001' , '李大钊' , NULL , '后勤部', NULL
UNION ALL SELEcT
'修改' , '2008-11-23 15:12' , '002' , '王美丽' , NULL , '技术部' , NULL
UNION ALL SELEcT
'修改' , '2008-12-1 09:10' , '001' , '李大钊' , NULL , NULL , '离职' DECLARE @time datetime
SET @time='2008-12-31 12:30' --查询日间SELECT @time as 查询时间,UpdTime as 修改时间,
Sno,Name,
sex=CASE WHEN sex is null then
(
SELECT TOP 1 sex
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=a.UpdTime
AND Sex Is NOT NULL
ORDER BY UpdTIme DESC
)
ELSE sex
END,
dept=CASE WHEN dept is null then
(
SELECT TOP 1 dept
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=a.UpdTime
AND dept Is NOT NULL
ORDER BY UpdTIme DESC
)
ELSE dept
END,
sts=CASE WHEN sts is null then
(
SELECT TOP 1 sts
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=a.UpdTime
AND sts Is NOT NULL
ORDER BY UpdTIme DESC
)
ELSE sts
END
FROM @t a WHERE UpdTime=
(
SELECT TOP 1 UpdTime
FROM @t
WHERE Sno=a.Sno
ORDER BY UpdTime DESC
)
ORDER BY Sno
DECLARE @t TABLE
(flag varchar(4), UpdTime datetime,Sno varchar(10),Name varchar(10),sex varchar(10),dept varchar(10),
sts varchar(10)
)
INSERT INTO @t
SELECT
'新增' , '2008-9-1 12:01' , '001' , '李大钊' , '男' , '业务部' , '在职'
UNION ALL SELEcT
'新增' , '2008-9-1 12:05' , '002','王美丽' , '女' , '财务部' , '在职'
UNION ALL SELEcT
'修改' , '2008-10-12 08:21' , '001' , '李大钊' , NULL , '后勤部', NULL
UNION ALL SELEcT
'修改' , '2008-11-23 15:12' , '002' , '王美丽' , NULL , '技术部' , NULL
UNION ALL SELEcT
'修改' , '2008-12-1 09:10' , '001' , '李大钊' , NULL , NULL , '离职' DECLARE @time datetime
SET @time='2008-12-31 12:30'SELECT @time as 查询时间,UpdTime as 修改时间,
Sno,Name,
sex=CASE WHEN sex is null then
(
SELECT TOP 1 sex
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=a.UpdTime
AND Sex Is NOT NULL
ORDER BY UpdTIme DESC
)
ELSE sex
END,
dept=CASE WHEN dept is null then
(
SELECT TOP 1 dept
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=a.UpdTime
AND dept Is NOT NULL
ORDER BY UpdTIme DESC
)
ELSE dept
END,
sts=CASE WHEN sts is null then
(
SELECT TOP 1 sts
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=a.UpdTime
AND sts Is NOT NULL
ORDER BY UpdTIme DESC
)
ELSE sts
END
FROM @t a WHERE UpdTime=
(
SELECT TOP 1 UpdTime
FROM @t
WHERE Sno=a.Sno
AND UpdTime<=@Time
ORDER BY UpdTime DESC
)
ORDER BY Sno
(
@NowDate datetime
)
select * from where datediff(day,[time],@NowDate)>0