求高效的查询语句！

[mybread]

有一个职务聘任表（ID，人员ID，聘任时间，聘任名称）
我想得到某个时间（可以假定时间为2005-05-06）所有人的职务聘任现状。
如有如下数据
ID ，人员ID ，聘任时间 ，聘任名称
1 2 1998-8-9 科员
2 3 2001-5-6 主任
3 2 2001-8-9 科长
4 2 2004-5-1 主任
5 4 2006-8-9 局长

如果想查询2005-05-06的职务聘任现状的话可得到下面数据
ID ，人员ID ，聘任时间 ，聘任名称
2 3 2001-5-6 主任
4 2 2004-5-1 主任


不知道有什么效率高的查询语句呢？

[-狙击手-]

declare @t table(ID int,人员ID int,聘任时间 datetime ,聘任名称 char(6))
insert @t select 1,2,'1998-8-9','科员' 
insert @t select 2,3,'2001-5-6','主任' 
insert @t select 3,2,'2001-8-9','科长' 
insert @t select 4,2,'2004-5-1','主任' 
insert @t select 5,4,'2006-8-9','局长' 


select *
from @t a
where not exists(select 1 from @t where 人员ID = a.人员ID and datediff(d,聘任时间 , a.聘任时间) < 0)
and datediff(d,聘任时间,'2005-05-06')>=0

/*

ID          人员ID        聘任时间                                                   聘任名称   
----------- ----------- ------------------------------------------------------ ------ 
2           3           2001-05-06 00:00:00.000                                主任  
4           2           2004-05-01 00:00:00.000                                主任  

*/

[-狙击手-]

（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）

ID          人员ID        聘任时间                                                   聘任名称   
----------- ----------- ------------------------------------------------------ ------ 
2           3           2001-05-06 00:00:00.000                                主任  
4           2           2004-05-01 00:00:00.000                                主任  

（所影响的行数为 2 行）

[mybread]

declare @t table(ID int,人员ID int,聘任时间 datetime ,聘任名称 char(6))
insert @t select 1,2,'1998-8-9','科员'
insert @t select 2,3,'2001-5-6','主任'
insert @t select 3,2,'2001-8-9','科长'
insert @t select 4,2,'2004-5-1','主任'
insert @t select 5,4,'2006-8-9','局长'
insert @t select 6,5,'1995-07-01','科员'
insert @t select 7,5,'2000-07-01','科长'
insert @t select 8,5,'2005-07-01','局长'


select *
from @t a
where not exists(select 1 from @t where 人员ID = a.人员ID and datediff(d,聘任时间 , a.聘任时间) < 0)
and datediff(d,聘任时间,'2005-05-06')>=0

为什么我再插入一个人员ID为5的一些记录时，显示还只是显示出人员ID为3和2的数据呢？

[mybread]

不知道有没有其它更好的查询语句呢？

[mybread]

希望无枪的狙击手再来狙击一下。

[mybread]

我用下面的语句，但总觉得效率不是很好，希望有更好效率的办法：
select b.人员id,b.聘任时间,b.聘任职称 from @t b inner join (select 人员id,max(聘任时间) as 聘任时间from @t where datediff(d,聘任时间,'2005-05-06')>=0 group by 人员id) a on b.聘任时间=a.聘任时间 and b.人员id=a.人员id

[mybread]

应该会有效率高的吧？
我的数据库里只有6万多条记录，但是我查询出来再插入到一个临时表却用了14多秒

[-狙击手-]

先看看结果对不对?

declare   @t   table(ID   int,人员ID   int,聘任时间   datetime   ,聘任名称   char(6)) 
insert   @t   select   1,2, '1998-8-9 ', '科员 '   
insert   @t   select   2,3, '2001-5-6 ', '主任 '   
insert   @t   select   3,2, '2001-8-9 ', '科长 '   
insert   @t   select   4,2, '2004-5-1 ', '主任 '   
insert   @t   select   5,4, '2006-8-9 ', '局长 '   
insert   @t   select   6,5, '1995-07-01 ', '科员 ' 
insert   @t   select   7,5, '2000-07-01 ', '科长 ' 
insert   @t   select   8,5, '2005-07-01 ', '局长 ' 


select * into # from @t where datediff(d,聘任时间, '2005-05-06 ')> 0

select   * 
from   #   a 
where not exists(select   1   
                 from   #   
                 where  ( 人员ID   =   a.人员ID   and   datediff(d,聘任时间   ,   a.聘任时间) <  0) )

drop table #

/*



ID          人员ID        聘任时间                                                   聘任名称   
----------- ----------- ------------------------------------------------------ ------ 
2           3           2001-05-06 00:00:00.000                                主任  
4           2           2004-05-01 00:00:00.000                                主任  
7           5           2000-07-01 00:00:00.000                                科长  
*/

[mybread]

速度是快了一点，变为了12秒。但这样的速度好像还是太慢了！

[-狙击手-]

try :

select * into # from @t where 聘任时间< '2005-05-06 23:59:59'
select   * 
from   #   a 
where not exists(select   1   
                 from   #   
                 where 人员ID   =   a.人员ID   and   聘任时间  > a.聘任时间)

drop table #

[mybread]

6万多的数据要用10多秒的时间好像有点不正常？不知道是不是数据结构没有设置好还是什么原因？

[-狙击手-]

比较一个父结果集和子结果集not exists已经是最高了


try :

select   * 
from  (select * from @t where datediff(d,聘任时间, '2005-05-06 ')> 0)  a 
where not exists(select   1   
                 from  ( select * from @t where datediff(d,聘任时间, '2005-05-06 ')> 0) b   
                 where  ( 人员ID   =   a.人员ID   and   datediff(d,聘任时间   ,   a.聘任时间) <  0) )

[-狙击手-]

并建议在聘任时间加索引　

[正牌风哥]

datediff(d,聘任时间, '2005-05-06 ')> =0
--2005-07-01 时间这里限制

[mybread]

哦，那你们可能是理解错了！
2005-05-06 的时候ryid为5的职务是科长。因为他2000-07-01的时候是‘科长’，所以2005-05-06 的时候ryid为5的职务也还是科长

[mybread]

终于发现总是了，我插入临时表时用的是insert into # select *
今天改为了无枪的狙击手的select * into #
查询时间马上变为了2秒以内，不知道是不是这个问题！

[-狙击手-]

我一直以为你用的我的方法，嘿，汗

[-狙击手-]

好像理论上不提倡select into，因为select into可能会引起表上锁而影响他人使用

[mybread]

我以为insert into和select into是一样的，所以我只改后面的查询语句……

[-狙击手-]

哈哈

关注 结果