求教:怎样得到小于给定日期的最大日期?

如果编号相同则选给定日期的最大记录,如下: 
  编号             日期               字段1       字段2         ... 
  1000         2007-11-01           1         a           ... 
  0099         2007-12-10           2         b           ... 
  1000         2007-12-08           2         c           ... 
  1001         2007-02-02           0         c           ... 
  0099         2007-12-10           0         l           ... 
  0099         2007-12-20           5         w           ...
  ...             ...               .         .           ... 给定日期:2007-12-10
得到: 
  编号             日期               字段1       字段2         ... 
  0099         2007-12-10           2         b           ... 
  1000         2007-12-08           2         c           ... 
  1001         2007-02-02           0         c           ...
若得到的记录中仍有2条以上日期相同的记录则:
1.任选一条
2.取字段1最大者
1和2语句应如何写,谢谢。

解决方案 »

  1.   

    select *
    from tablename t
    where not exists(select 1 from tablename where a.编号 = 编号 and abs(datediff(s,日期,'2007-12-10'))> abs(datediff(s,a.日期,'2007-12-10')))
      

  2.   

    create table testTable(UID int,inputTime datetime,EID int)
    insert into testTable values(1,'2007-10-1 10:00',1)
    insert into testTable values(2,'2007-10-1 11:00',1)
    insert into testTable values(3,'2007-10-1 12:00',1)
    insert into testTable values(4,'2007-11-1 10:00',2)
    insert into testTable values(5,'2007-11-1 11:00',2)
    insert into testTable values(6,'2007-11-1 12:00',2)
    go
    select *
    from testtable a
    where  not exists(select 1 
                      from testtable 
                      where a.eid = eid 
                      and abs(datediff(s,inputtime ,'2007-10-15 11:00:00'))< abs(datediff(s,a.inputtime ,'2007-10-15 11:00:00'))
     )drop table testTable/*
    UID         inputTime                                              EID         
    ----------- ------------------------------------------------------ ----------- 
    3           2007-10-01 12:00:00.000                                1
    4           2007-11-01 10:00:00.000                                2（所影响的行数为 2 行）
    */
      

  3.   

    编号                 日期                字段1          字段2          ...   
    1000          2007-11-01             1               a           ...   
    0099          2007-12-10             2               b           ...   
    1000          2007-12-08             2               c           ...   
    1001          2007-02-02             0               c           ...   
    0099          2007-12-10             0               l           ...   
    0099          2007-12-20             5               w           ... 
    1000          2007-12-08             2               c           ...   
    0098          2007-09-09             1               q           ...
    ...               ...                .               .           ...   
    给定日期:2007-12-10 
    得到:   
     编号                 日期               字段1           字段2         ...   
    0098             2007-09-09           1               q          ...
    0099             2007-12-10           2               b          ...   
    1000             2007-12-08           2               c          ...   
    1001             2007-02-02                       
      

  4.   

    编号            日期               字段1          字段2          
    1000       2007-11-01           1             a       
    0099       2007-12-10           2             b        
    1000       2007-12-08           2             c
    1001       2007-02-02           0             c     
    0099       2007-12-10           0             l
    0099       2007-12-20           5             w
    1000       2007-12-08           2             c
    0098       2007-09-09           1             q
        
    给定日期:2007-12-10   
    得到:       
    编号            日期               字段1          字段2
    0098       2007-09-09           1              q
    0099       2007-12-10           2              b     
    1000       2007-12-08           2              c
    1001       2007-02-02           0              c                                     
      

  5.   

    --假设表名为t1
    --1.
    select * from t1 a where exists(select * from (select top 1 * from t1 where 编号=a.编号 order by 日期) b where 日期=a.日期 and 字段1=a.字段1)
    --2.
    select * from t1 a where exists(select * from (select top 1 * from t1 where 编号=a.编号 order by 日期,字段1) b where 日期=a.日期 and 字段1=a.字段1)
      

  6.   

    改正一下,上面写成了最小的
    --假设表名为t1
    --1.
    select * from t1 a where exists(select * from (select top 1 * from t1 where 编号=a.编号 order by 日期 desc) b where 日期=a.日期 and 字段1=a.字段1)
    --2.
    select * from t1 a where exists(select * from (select top 1 * from t1 where 编号=a.编号 order by 日期 desc,字段1 desc) b where 日期=a.日期 and 字段1=a.字段1)
    只要编号,日期,字段1不重复,就不会重复
      

  7.   

    declare @T table(
    编号 char(4),                        
    日期 datetime,                             
    字段1 int,
    字段2  nvarchar(2))
                
    insert @T select '1000',               '2007-11-01',                       1,                           'a'               
    insert @T select '0099',               '2007-12-10',                       2,                           'b'                 
    insert @T select '1000',               '2007-12-08',                       2,                           'c' 
    insert @T select '1001',               '2007-02-02',                       0,                           'c'           
    insert @T select '0099',               '2007-12-10',                       0,                           'l' 
    insert @T select '0099',               '2007-12-20',                       5,                           'w' 
    insert @T select '1000',               '2007-12-08',                       2,                           'c' 
    insert @T select '0098',               '2007-09-09',                       1,                           'q' 
    select 
    distinct * 
    from 
    @T a 
    where 
    字段1=(select top 1 字段1 from @T 
    order by case when 编号=a.编号  and 日期='2007-12-10'  then 1 
    when 编号=a.编号 then 2 else 3 end asc, 字段1 desc)
    编号   日期                                                     字段1         字段2  
    ---- ------------------------------------------------------ ----------- ---- 
    0098 2007-09-09 00:00:00.000                                1           q
    0099 2007-12-10 00:00:00.000                                2           b
    1000 2007-12-08 00:00:00.000                                2           c
    1001 2007-02-02 00:00:00.000                                0           c（所影响的行数为 4 行）
      

  8.   

    如果字段不相同时这样取:
    declare @T table(
    编号 char(4),                        
    日期 datetime,                             
    字段1 int,
    字段2  nvarchar(2))
                
    insert @T select '1000',               '2007-11-01',                       1,                           'a'               
    insert @T select '0099',               '2007-12-10',                       2,                           'b'                 
    insert @T select '1000',               '2007-12-08',                       2,                           'c' 
    insert @T select '1001',               '2007-02-02',                       0,                           'd'           
    insert @T select '0099',               '2007-12-10',                       0,                           'e' 
    insert @T select '0099',               '2007-12-20',                       5,                           'f' 
    insert @T select '1000',               '2007-12-08',                       2,                           'h' 
    insert @T select '0098',               '2007-09-09',                       1,                           'g' 
    select 
     * 
    from 
    @T a 
    where 
    checksum(字段1,字段2)=(select top 1 checksum(字段1,字段2) from @T 
    order by case when 编号=a.编号  and 日期='2007-12-10'  then 1 
    when 编号=a.编号 then 2 else 3 end asc, 字段1 desc)
    order by 编号 asc
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）
    （所影响的行数为 1 行）编号   日期                                                     字段1         字段2  
    ---- ------------------------------------------------------ ----------- ---- 
    0098 2007-09-09 00:00:00.000                                1           g
    0099 2007-12-10 00:00:00.000                                2           b
    1000 2007-12-08 00:00:00.000                                2           c
    1001 2007-02-02 00:00:00.000                                0           d（所影响的行数为 4 行）
      

  9.   

    declare @t table (编号 varchar(10),日期 datetime,字段1 int,字段2 varchar(2))
    insert @t select
    '1000','2007-11-01',1,'a' union select 
    '0099','2007-12-10',2,'b' union select 
    '1000','2007-12-08',2,'c' union select 
    '1001','2007-02-02',0,'c' union select 
    '0099','2007-12-10',0,'l' union select 
    '0099','2007-12-20',5,'w' union select 
    '1000','2007-12-08',2,'c' union select 
    '0098','2007-09-09',1,'q'
    select * into #
    from @t a
    where not exists(select 1 
                      from @t  
                      where a.编号 = 编号 and ( 
                      abs(datediff(s,日期 ,'2007-10-15')) < abs(datediff(s,a.日期 ,'2007-10-15')))
     )select *
    from # a
    where not exists(select 1 from # where a.编号 = 编号 and 字段1> a.字段1)
    /*编号         日期                                                     字段1         字段2  
    ---------- ------------------------------------------------------ ----------- ---- 
    0098       2007-09-09 00:00:00.000                                1           q
    0099       2007-12-10 00:00:00.000                                2           b
    1000       2007-11-01 00:00:00.000                                1           a
    1001       2007-02-02 00:00:00.000                                0           c（所影响的行数为 4 行）
    */
    drop table #
      

  10.   

    '1000','2007-12-08',2,'c' union select 1000好像应该选这一条吧
      

  11.   

    谢谢各位!
    roy_88 的方法能满足要求,但运行时间太长。数据总记录数约30000行,满足要求的约8000行,运行了6分钟仍没有出结果。
    happyflystone 的方法仍有日期重复记录。
    由于昨晚和今日上午有事未及时告知结果,表示抱歉。
      

  12.   


    CREATE TABLE #
    编号 char(4),                        
    日期 datetime,                             
    字段1 int,
    字段2  nvarchar(2))
                
    insert @T select '1000',               '2007-11-01',                       1,                           'a'               
    insert @T select '0099',               '2007-12-10',                       2,                           'b'                 
    insert @T select '1000',               '2007-12-08',                       2,                           'c' 
    insert @T select '1001',               '2007-02-02',                       0,                           'c'           
    insert @T select '0099',               '2007-12-10',                       0,                           'l' 
    insert @T select '0099',               '2007-12-20',                       5,                           'w' 
    insert @T select '1000',               '2007-12-08',                       2,                           'c' 
    insert @T select '0098',               '2007-09-09',                       1,                           'q' 
    SELECT MAX(日期),编号
    FROM #
    WHERE CONVERT(VARCHAR(10),日期,120) < '2007-12-10 '
    GROUP BY [编号]SELECT MAX(编号)
    FROM 
    (
    SELECT aa=MAX(日期),编号
    FROM #
    WHERE CONVERT(VARCHAR(10),日期,120) < '2007-12-10 '
    GROUP BY [编号]
    )
    T
      

  13.   

    KasenHOo:
    你的方法可以,但其它字段如何取?谢谢!