表结构是这样的。
num   name          state   
1      a              4       
2      d              5           
1      a              4        
2      d              6                       
1      a              5       
3      v              4         
1      a              6      
需要的数据是:
1      a              6
2      d              6
3      v              4
请问这个该如何筛选?               
                                        
                                          
                                                                                                     

解决方案 »

  1.   

    --> 测试数据: #tb
    if object_id('tempdb.dbo.#tb') is not null drop table #tb
    go 
    create table #tb (num int,name varchar(1),state int)
    insert into #tb
    select 1,'a',4 union all
    select 2,'d',5 union all
    select 1,'a',4 union all
    select 2,'d',6 union all
    select 1,'a',5 union all
    select 3,'v',4 union all
    select 1,'a',6select * from #tb t
    where not exists(select * from #tb where num=t.num and state>t.state)num         name state
    ----------- ---- -----------
    2           d    6
    3           v    4
    1           a    6(3 行受影响)
      

  2.   


    SELECT num,NAME,Max(state) FROM tab GROUP BY num,name